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

10 comentarios »

  1. Muchas gracias por la información, sin duda será de gran utilidad…

    Comentario por Junatencia — junio 4, 2009 @ 1:22 pm

  2. Mi duda es si existe algunas otras cosas en SQL2008 para un sistema formal, seguridad, integridad etc…

    Comentario por Anónimo — diciembre 8, 2009 @ 7:53 pm

  3. Hasta ahora sale en sqlsever, que basura de motor de bd, oracle lo tiene desde antes del 2001, osea 10 años de atraso.

    Comentario por Erick — febrero 16, 2011 @ 8:53 pm

    • Esto fue en el 2008, o sea en todo caso, 7 años de atraso…

      Comentario por grimpi — febrero 16, 2011 @ 9:58 pm

    • hermano pues si lo le gusta pues no comente asi de sencillo

      Comentario por Anónimo — febrero 4, 2013 @ 3:19 pm

  4. Muy provechoso el artículo. Quisiera saber si existen también las herramientas necesarias para realizar validaciones de subconjuntos de datos en el merge. Me explico, cuando se realiza el merge de una BD1 a otra BD2 para una tabla1, los valores de las PK’s de la BD2 en la misma tabla serán diferentes, por lo que al intentar realizar el merge de una tabla2 que referencia a la tabla1, la cual ya fue mezclada, los valores de las referencias serán diferentes. ¿Como controlar este tipo de situaciones?

    Comentario por john arley jimenez — mayo 20, 2011 @ 5:10 pm

  5. Una pregunta, cuando dices “el operador MERGE JOIN resulta el método más eficiente ” en caso de tablas con indices….
    No encontrado información sobre MERGE JOIN… (O no he sabido buscarla), me puedes dar un ejemplo de su uso?
    Saludos
    Gorka

    Comentario por Gorka — septiembre 20, 2011 @ 7:14 am

  6. Howdy I am so grateful I found your webpage, I really found you by mistake, while I was looking on Google for something else, Nonetheless I
    am here now and would just like to say kudos for a remarkable
    post and a all round enjoyable blog (I also love the theme/design),
    I don’t have time to read through it all at the moment but I have saved it
    and also added your RSS feeds, so when I have time I will be
    back to read more, Please do keep up the great b.

    Comentario por maryland car accident attorney — mayo 17, 2013 @ 4:04 am

  7. If you are not ready to fight with yourself, to say “NO” to things you liked to do or
    which were just a habit to you then nothing
    on Earth will help you to get rid of those excess pounds.
    This gadget is one of the best buys around if you are looking for the cheapest
    Internet tablets. Aided by the Pills rapidly getting best tablet
    pc all of the direct device when using the iphone, it is really distinct that we should expect to witness it develop
    worldwide recognition for countless years.

    Comentario por ϢотограϢии — mayo 17, 2013 @ 5:36 am

  8. Es muy bueno el ejemplo, pero que pasa si las dos bases del merge, existen en diferentes servidores. Como le indicamos esto a la sentencia. En mi caso cuando me paro en un servidor, no ve al otro y viceversa.

    Comentario por Carlos Campos — enero 8, 2014 @ 9:32 pm


RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Blog de WordPress.com.

A %d blogueros les gusta esto: