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
