Grimpi IT Blog

febrero 24, 2009

Lo nuevo de SQL Server 2008: MERGE

Filed under: SQL Server, SQL Server 2008, T-SQL — Etiquetas: — grimpi @ 11:22 pm

Otra de las características interesantes que Microsoft incorporó en SQL Server 2008 y que al igual que la sentencia GROUPING SETS, ya existía en otros motores de bases de datos, es la clausula MERGE (de la cual habíamos hablado brevemente antes).
Esta cláusula nos va a permitir definir lógica de combinación para operaciones atómicas de inserción, borradas y actualización de datos.
La idea es comparar 2 conjuntos de datos y detectar las diferencias de datos entre las 2 tablas y en función de eso, ejecutar alguna operación de actualización sobre la tabla.
La clausula MERGE nos sirve básicamente para 2 cosas:

1) Sincronizar los datos de 2 tablas. Supongamos que tenemos 2 bases distintas (Producción y Desarrollo por ejemplo) y queremos sincronizar los datos de una tabla para que queden exactamente iguales. Lo que antes hubiese implicado
algunas sentencias mezcladas con INNER JOIN y NOT EXISTS, ahora es posible resumirlo en una operación atómica mucho
más sencilla y eficiente.

2) La otra razón por la cual podríamos usar MERGE, es cuando tenemos nuevos datos que queremos almacenar en una tabla y no sabemos si la primary key de la tabla ya existe o no, por lo tanto, no sabemos si hacer un UPDATE o un INSERT en la tabla. El famoso IF EXISTS… (tema que ya habíamos visto acá y que es un poquito más complicado de lo que parece a simple vista).

Veamos un ejemplo:

MERGE dbo.Tabla1 AS Target
USING (SELECT ID,Campo1,Campo2,Campo3 FROM dbo.Tabla2) AS Source
ON (Target.ID = Source.ID)
WHEN MATCHED THEN

UPDATE
SET Target.Campo1 = Source.Campo1, Target.Campo2 = Source.Campo2

WHEN NOT MATCHED BY TARGET THEN

INSERT (ID,Campo1,Campo2,Campo3)
VALUES (Source.ID,Source.Campo1,Source.Campo2, Source.Campo3)

WHEN NOT MATCHED BY SOURCE THEN

DELETE;

Realmente creo que es una de las cosas más interesantes que incorporó la nueva versión de SQL Server y es increíble que algo tan útil y tan práctico como esto, no hubiese estado antes.
Es importante entender que la ventaja del MERGE no es una simple cuestión sintáctica que nos permite escribir un par de líneas menos de código. La ventaja real y más importante está en que permite hacer sincronizar 1 tabla en función de una consulta, de manera atómica. Esto significa que si una operación de delete/insert/update falla, se hace un rollback de todo el conjunto de datos y más importante aún, se garantiza en escenarios de alta concurrencia, donde puede haber otros procesos escribiendo en la misma tabla en el mismo instante, no existan incoherencias.

Capturar salida:
Algo fantástico que existe a partir de SQL Server 2005, es la clausula OUTPUT que permite capturar todo lo que sucedió dentro de una operación INSERT/DELETE/UPDATE. En SQL Server 2008 el uso conjunto de MERGE + OUTPUT nos sirve saber que registros fueron modificados y que acción se hizo sobre ese registro (INSERT, UPDATE o DELETE).
La nueva función $action indica que operación se realizó, mientras que los atributos deleted e inserted guardan la información sobre el registro afectado (de la misma manera que funcionan con los triggers).

Ejemplo:

MERGE dbo.Tabla1 AS Target
USING (SELECT ID,Campo1,Campo2,Campo3 FROM dbo.Tabla2) AS Source
ON (Target.ID = Source.ID)
WHEN MATCHED THEN

UPDATE SET Target.Campo1 = Source.Campo1, Target.Campo2 = Source.Campo2

WHEN NOT MATCHED BY TARGET THEN

INSERT (ID,Campo1,Campo2,Campo3)
VALUES (Source.ID,Source.Campo1,Source.Campo2, Source.Campo3)

WHEN NOT MATCHED BY SOURCE THEN

DELETE

OUTPUT $action, deleted.*, inserted.*;

Resultado:

Avanzando en las profundidades del MERGE:
Si vemos el plan de ejecución, podemos encontrar cosas muy interesantes. En primer lugar, aparece un nuevo operador lógico en el plan, que se llama “Clustered Index Merge” y es nuevo de SQL Server 2008. Este operador en función de un conjunto de datos, realiza un delete, insert o update a una tabla usando su respectivo índice clustered. Si la tabla no tuviese un índice clustered, entonces se usaría el también nuevo operador “Table Merge”. Esta es una de las razones por la cual MERGE no es una simple encapsulación que simplifica cosas que ya podíamos hacer antes, sino que al ser una operación interna del motor de SQL Server, es más eficiente que cualquier otra alternativa existente en las versiones previas de SQL Server.
Otro punto interesante es que podemos ver es que cuando existen índices y las 2 tablas tienen similares volúmenes de datos, el operador MERGE JOIN resulta el método más eficiente en estas operaciones, ya ambas tablas son escaneadas una sola vez y no hay necesidad de ordenar los datos. Es posible cambiar estableciendo otro join hint, pero en la mayoría de los casos, el MERGE JOIN es lo más óptimo.


Algunas recomendaciones para obtener mejor performance con esta sentencia:

  • Crear un índice clustered sobre las columnas relacionadas en el JOIN, en la tabla destino.
  • Crear un índice único sobre las columnas relacionadas en el JOIN, en la tabla de origen (en caso que hubiese).

Esto garantiza al motor que no tiene que ejecutar ninguna validación adicional ni efectuar ningún sort extra.

Links:
http://technet.microsoft.com/en-us/library/cc879317.aspx
http://technet.microsoft.com/en-us/library/bb522522.aspx

Anuncios

febrero 12, 2009

Identity en SQL Server

Filed under: Identitys, SQL Server — Etiquetas: , — grimpi @ 11:25 pm

Quienes trabajaron mínimamente en alguna versión de SQL Server, conocerán los campos Identitys, que son columnas cuyo valor es autoincremental. Hay muchas discusiones sobre las ventajas y desventajas del uso de este tipo de campos, para las columnas que componen la clave primaria (PK), pero es algo que ya voy a tratar en otro artículo.
Por el momento muestro un listado de tips que cualquier programador que trabaje con SQL Server debería saber al respecto de los campos Identitys:

1) Resetear el valor del campo identity:

Mucha gente piensa que no es posible resetear el contador de un identity de una tabla. FALSO. Existen dos formas de hacerlo, una es mediante TRUNCATE TABLE. Cuando hacemos un TRUNCATE de una tabla, además de borrar todo su contenido, reseteamos el valor del campo Identity. Esto es muy obvio, porque un TRUNCATE TABLE no hace nada más y nada menos que borrar la tabla y volver a crearla.
La segunda opción (y la que nos interesa) es usar el comando DBCC CHECKIDENT. Esta instrucción permite setera el valor que queremos del campo Identity.
Su sintaxis es la siguiente:

DBCC CHECKIDENT <NombreDeTabla>,RESEED,NuevoValor

Hay que tener mucho cuidado con el uso de este comando, ya que podemos setear un valor menor al máximo de la tabla actual y crear en algún momento un valor duplicado. Una opción interesante de este comando es asignar al campo el valor máximo de la tabla + 1, y así garantizar que no tendremos problemas de registros duplicados, para eso solo hay que poner:

DBCC CHECKIDENT <NombreDeTabla>

2) Insertar valores explícitos en un campo Identity:

Cuando se inserta un registro en una tabla con un campo Identity, este se incrementa automáticamente y si queremos modificar o insertar este valor manualmente, el SQL nos tira error y nos indica que no es posible.
Sin embargo, algunas veces puede que necesitemos deshabilitar temporalmente la propiedad Identity, y poder ingresar un valor explícito a la tabla. Generalmente queremos hacer algo así cuando hacemos copias o replicaciones de una tabla de una base a otra tabla.
Para esto, tenemos la opción:


SET IDENTITY_INSERT <NombreDeTabla> ON

Con esta opción, podemos insertar valores en un campo Identity como si fuese un campo más. Luego, cuando terminamos de insertar valores, hay que volver habilitar nuevamente la propiedad Identity del campo. Para eso, corremos el mismo comando anterior, pero con el parámetro OFF.

SET IDENTITY_INSERT <NombreDeTabla> OFF

3) Capturar el valor en un INSERT:

Muchas veces cuando tenemos una tabla con un campo Identity, al hacer un INSERT, queremos saber cuál es el valor que insertó en este campo.
Lo mas común en estos casos es usar @@Identity, que es una variable global que indica el valor del último campo identity insertado en cualquier tabla de la base. Esto último es un detalle muy importante. Si la tabla contiene un trigger que inserta registros en otra tabla, o antes de leer la variable @@Identity, se inserta otro registro en cualquier tabla de la base, el valor @@Identity, tendrá un valor diferente al que estábamos esperando.
Para resolver esta situación, tenemos dos funciones muy útiles: SCOPE_IDENTITY e IDENT_CURRENT. La función SCOPE_IDENTIY nos devuelve el último valor generado dentro de un scope, o sea dentro de un entorno, ya sea un Store Procedure, Function o Trigger. En nuestro caso anterior, como el trigger esta fuera del scope, la función SCOPE_IDENTIY nos devolvería el valor que queremos.
Pero por otro lado, tenemos la función, IDENT_CURRENT, donde se le pasa por parámetro el nombre de la tabla y nos devuelve el ultimo valor identity generado para esta tabla, sin importar el scope. Esta opción en mi opinión es la más clara. Su sintaxis es la siguiente:

SELECT IDENT_CURRENT(‘NombreTabla’)

4) Capturar los valores Identity en un INSERT con multiples registros:

Supongamos que insertamos en una sola sentencia INSERT, mas de un registro en una tabla. Como hacemos para capturar el valor de la columna identity de todos los nuevos registros insertados?. Ni la funcion IDENT_CURRENT ni SCOPE_IDENTITY nos sirven, ya que estas solo devuelven el ultimo valor insertado, pero a nosotros nos interesan todos los valores insertados. Veamos este ejemplo:

CREATE TABLE Cliente (ClienteID INT IDENTITY, Nombre VARCHAR(50))

INSERT INTO Cliente
SELECT ‘Esteban’
UNION
SELECT ‘Juan’
UNION
SELECT ‘Ricardo’

SELECT IDENT_CURRENT(‘Cliente’)

Como ya dijimos antes, IDENT_CURRENT solo nos va a devolver el valor del identity del registro ‘Ricardo’, que fue el último registro que se insertó, por lo tanto, el ejemplo anterior no nos sirve.
Pero a partir de SQL Server 2005, existe la interesante clausula OUTPUT, que permite capturar el valor de las operaciones INSERT y DELETE.
Veamos entonces este ejemplo, que usa OUTPUT y almacena los valores de los registros insertados en una variable de tabla.

DECLARE @NuevosIdentitys TABLE (ID INT)

INSERT INTO Cliente
OUTPUT INSERTED.ClienteID INTO @NuevosIdentitys(ID)

SELECT ‘Esteban’
UNION
SELECT ‘Juan’
UNION
SELECT ‘Ricardo’

SELECT * FROM @NuevosIdentitys

5) Detectar si una columna es Identity:

Usando la función COLUMNPROPERTY, podemos obtener esta información.

SELECT COLUMNPROPERTY(OBJECT_ID(‘<NombreDeTabla>’),’<NombreColumna>‘,‘IsIdentity’)

Otra opción, más potente, consiste en usar la vista sys.identity_columns
(solo a partir de SQL Server 2005), que además de determinar si una columna es Identity, nos permite obtener mayor información sobre la columna relacionada con esta propiedad, como por ejemplo el valor inicial, el valor de incremento, si se aplica o no también para casos de replicación (IsNotForReplication), etc.

SELECT
Name,
is_identity,
seed_value,
increment_value,
is_not_for_replication,
last_value
FROM sys.identity_columns

6) Obtener el valor de la columna Identity aun si saber el nombre del campo:

Supongamos que queremos obtener el valor de una columna que sabemos que es identity, pero solo sabemos el nombre de la tabla (en situaciones donde ejecutamos SQL dinámico podría llegar a darse un caso así).
Existe en SQL Server una función que se llama IDENTITYCOL, que precisamente nos devuelve este valor que queremos averiguar.

SELECT IDENTITYCOL FROM <NombreDeTabla>

Actualizacion:
Hice un nuevo post con mas tips de como trabajar con columnas identities.

febrero 6, 2009

Introducción a Test Driven Development (TDD)

Filed under: Arquitectura, Patrones, Test Driven Development — Etiquetas: — grimpi @ 10:46 pm

Test Driven Development (TDD) es una metodología de programación que involucra 2 prácticas: Escribir primero las pruebas unitarias, o sea, escribir el test antes de escribir el programa y refactoring.
Yo creo que una de las cosas más difíciles al trabajar de esta manera, es cambiar la mentalidad con la que estamos acostumbrados a desarrollar.
TDD es un proceso interactivo donde se repiten una serie de pasos hasta que se está satisfecho con el código generado. A TDD no le importa demasiado como está estructurado el código ni la arquitectura del software. La manera en que TDD trabaja es mediante requerimientos o casos de usos, que son descompuestos en un conjunto de comportamientos más chicos necesarios para cumplir con el caso de uso dado. Luego por cada comportamiento del sistema, lo primero que se debe hacer es escribir un test unitario para dicha funcionalidad.

Hay muchos conceptos confusos alrededor de esta técnica que me gustaría aclarar:

  • Primero las pruebas unitarias, luego el código de los métodos (Test-First Development). Este concepto es una de las características fundamentales de TDD. Si no respetamos esto, no estamos haciendo TDD (lo que tampoco tiene porque estar mal, pero es necesario hacer la aclaración sobre lo que no es TDD).
  • TDD no es una técnica de testing. TDD apunta a desarrollar software de manera incremental de la forma más simple y robusta posible.
  • Mucha gente tiene cierto rechazo porque piensa que TDD consiste en escribir TODAS las pruebas unitarias antes que el código. Esto es falso y es importante remarcarlo. TDD consiste en hacer un desarrollo cíclico e incremental. Se debe generar inicialmente un conjunto muy limitado de test que corresponden a una clase o caso de uso, para luego ir desarrollando el código. Una vez que toda la funcionalidad correspondiente a las pruebas unitarias fueron desarrolladas, se pasa a otro modulo.
  • A pesar de lo que siempre se dice que en TDD lo primero que hay que hacer es escribir el test unitario antes del código, esto tiene un límite obvio y es que la estructura de la entidad que vamos a probar ya debe estar creada. Por ejemplo, si vamos a desarrollar una clase que administre clientes, conviene primero crear la clase entidad Cliente, con sus respectivas propiedades, luego las pruebas unitarias y por último los métodos de la clase Cliente (que pueden ser insertar, modificar, validar, etc.).

Ciclo del TDD:

1) Escribir un test e intentar compilar, aunque aun no sea posible porque no se desarrollo la funcionalidad correspondiente.

2) Escribir la mínima cantidad de código posible para que la prueba unitaria compile y ejecutarla. Deberá fallar, ya que no tiene la funcionalidad requerida.

3) Escribir el test de tal manera, que al ser ejecutado, este no falle. La idea es que la función escrita no sea lo más elegante y optimo posible, sino que simplemente, permita pasar el test unitario exitosamente.

4) Refactorizar el código de la función, para que sea lo más optimo posible. Es muy posible que existan varias instancias de refactorización del código de la función en distintas etapas del proyecto.

5) Escribir un nuevo test unitario para otra funcionalidad.


Ventajas:

Todo muy lindo lo explicado, pero que ventajas concretas ofrece trabajar con TDD? Porque conviene trabajar con esta metodología?

  • En primer lugar y la ventaja más obvia, es que al tener el test unitario escrito por cada método, podemos detectar regresiones y cambios en una función que rompan otra parte del código.
  • Mayor facilidad para refactorizar y mantener código.
  • Si bien es verdad que TDD significa en un principio escribir más código, la realidad indica que muchas veces ahorra código y tiempo, al disminuir la cantidad de defectos en los métodos.
  • Al desarrollar el método de acceso al método antes que su implementación, ayuda a pensar cómo utilizar el componente y para que debería existir, por lo tanto, nos ayuda al diseño del software.

Recomendaciones:

  • Hacer más de un test unitario por función que contemplen distintas situaciones. Se deben probar la mayor cantidad de casos posibles. Es MUY recomendable hacer un test unitario negativo, o sea, que si no devuelve una excepción o el resultado de llamar al método es distinto de false, es porque hay un error. Por ejemplo, si es una regla de negocio, hacer un test unitario para que contemple la validación exitosa y otro test unitario, que llame al método con parámetros incorrectos para probar que la validación la está haciendo de manera correcta.
  • No meter lógica en los test unitarios. Si queremos probar distintas situaciones, usemos un test unitario por cada prueba.
  • No hacer que un test unitario dependa de otro test. Deben ser lo más independientes posibles uno del otro.

TDD vs TAC:

Uno de los componentes principales de TDD es Test First Development, que como ya dijimos antes, consiste en escribir el test unitario antes que el código. Pero sin embargo, existe otro enfoque más tradicional, que es precisamente el inverso. Escribir el test unitario DESPUES de haber escrito el código y a esto se lo llama Test After Coding (TAC).

Escribir antes el test unitario nos garantiza que todos los métodos y funciones de nuestra aplicación, tengan su respectivo test unitario. En cambio, esto en la práctica, no está garantizado con TAC, ya que siempre se va a estar corriendo con los tiempos.

Por otro lado, TDD nos ayuda a pensar el diseño del software, ya que cuando escribimos el test unitario, debemos pensar en la interfaz del método y como va a interactuar. Si empiezo diseñando la prueba, se hace más claro que tiene que hacer exactamente el método, con lo cual, se podría reducir tiempos y sobre todo, posibilidad de errores.

Otro punto a favor de TDD y muy importante, la primera vez que se ejecuta el test unitario, este debe fallar. Si el test al ser ejecutado, pasa exitosamente, es porque está mal escrito. Sin embargo, si el test fue escrito posteriormente al desarrollo del método, existe la posibilidad de no poder detectar que el test unitario en si mismo este mal escrito, ya que si siempre se ejecuta exitosamente, uno tiende a pensar que el método esta correcto y dificulta la tarea de ver si el test unitario está correcto. Esto es fundamental.

Ejemplo de código:

Pongamos un poco de TDD en acción y veamos un ejemplo simple pero práctico de cómo usar esta metodología de trabajo.

Supongamos que nos piden desarrollar modulo que permita insertar, modificar, listar y eliminar clientes. A su vez también tenemos que programar un método de validación que cumpla con la siguiente regla de negocio del cliente: Debe tener menos de 50 años, el sexo solo puede ser F o M, y el nombre es un campo obligatorio.

Para simplificar, vamos a usar el patrón Active Record (del cual ya habíamos hablado antes), que nos permite en una misma clase poner la lógica de negocio y de acceso a datos.

public class Cliente
{

private string nombre;
private int edad;
private string sexo;

public string Sexo
{

get { return sexo; }
set { sexo = value; }

}

public int Edad
{

get { return edad; }

set { edad = value; }

}

public string Nombre
{

get { return nombre; }
set { nombre = value; }

}

}

Ahora debemos crear la clase ClienteTest y para reducir el ejemplo, vamos a crear solamente 2 pruebas unitarias (Validar e Insertar)

  • 1° Fase

[TestClass()]

public class ClienteTest

{

[TestMethod()]

public void InsertarTest()

{

Cliente nuevo = new Cliente();

nuevo.Edad = 35;

nuevo.Sexo = “F”;

nuevo.Nombre = “Alejandra”;

bool resultado = Cliente.Insert(nuevo);

Assert.AreEqual(resultado, true);

}

[TestMethod()]

public void ValidarTest()

{

Cliente nuevo = new Cliente();

nuevo.Edad = 50;

nuevo.Sexo = “F”;

nuevo.Nombre = “Alejandra”;

bool resultado = nuevo.Validate();

Assert.AreEqual(resultado, true);

}

}

Obviamente este codigo no va a compilar, ya que los metodos de la clase Cliente todavia no fueron siquiera declarados.

  • 2° Fase

Ahora debemos crear en la Clase Cliente, los 2 metodos que vamos a utilizar, pero con el UNICO objetivo que las pruebas unitarias puedan compilar y fallar al ser ejecutadas.


///
<summary>

/// Valida que el el cliente cumpla con las reglas de negocio.

///</summary>

public bool Validate()

{

return false;

}

///<summary>

/// Inserta un nuevo Cliente en la base de datos.

///</summary>

public static bool Insert(Cliente nuevo)

{

return false;

}

  • 3° Fase

Corremos todos los test unitarios desarrollados y estos deben fallar (Red Status)


  • 4° Fase

Desarrollamos los 2 métodos correspondientes a los test unitarios (para el segundo método, no lo desarrolle completo para no hacer largo el código y clarificar lo que se quiere hacer).


public bool Validate()

{

if (edad < 50) return false;

if (String.IsNullOrEmpty(sexo)) return false;

if (!sexo.Equals(“F”) && !sexo.Equals(“M”)) return false;

if (String.IsNullOrEmpty(nombre)) return false;

return true;

}

public static bool Insert(Cliente nuevo)

{

try

{

using (SqlConnection conn = new SqlConnection(“CONNECTION_STRING”))

{

using (SqlCommand command = new SqlCommand(“INSERT INTO CLIENTES …”))

{

command.ExecuteNonQuery();

}

}

return true;

}

catch

{

return false;

}

}

  • 5° Fase

Corremos todos los test unitarios desarrollados y ahora estos deben pasar exitosamente (Green Status)


Links:

Crea un blog o un sitio web gratuitos con WordPress.com.