Diferencias entre TRUNCATE TABLE y DELETE FROM

Una duda bastante habitual entre los desarrolladores, es cuál es la diferencia entre un TRUNCATE TABLE y un DELETE FROM TABLE. Este post (y el blog en general) está centrado en SQL Server, sin embargo, la mayoría de las diferencias entre ambas sentencias aplican a cualquier motor de bases de datos (Oracle, MySQL, DB2, etc).
Primero voy a enumerar las diferencias y luego voy a explicar el porqué de dichas diferencias.

TRUNCATE TABLE	DELETE FROM
Es una operación DDL.	Es una operación DML.
No permite el borrado selectivo. TRUNCATE TABLE elimina todo el contenido de la tabla.	Permite el borrado selectivo, mediante la clausula WHERE.
No se puede ejecutar, si la tabla tiene asociadas, aun si no existiesen registros en la tabla que contiene la FK.	Se puede ejecutar si hay FK asociadas a la tabla, pero siempre y cuando no tenga registros asociados o la FK este deshabilitada.
Es la forma más rápida de eliminar el contenido de una tabla.	Es más lenta.
No se activa ningún trigger al ejecutarse (a partir de SQL Server 2005, es posible capturar el evento mediante un DDL trigger, pero a modo de auditoría, no es posible tener acceso a los valores que fueron eliminados).	Puede activarse el trigger de ON DELETE y poder determinar que registros están siendo eliminados. siendo eliminados.
En caso que la tabla tuviese un campo Identity, se resetea el valor a 1 (o al valor base determinado en el campo).	No resetea el valor del campo Identity, en caso que la tabla tuviese uno.
TRUNCATE TABLE desasocia (deallocate) las páginas de datos de la tabla.	DELETE FROM marca cada registro afectado, como eliminado.
El logueo en el transaction log es mínimo. Solo registra el dealloc de las páginas de datos.	Loguea cada operación sobre los registros afectados.
No puede ser ejecutado si la tabla tiene asociadas vistas indexadas.	Se puede ejecutar si la tabla tiene vistas indexadas.

Como podemos ver, TRUNCATE TABLE es bastante más restrictivo que DELETE, al punto que en muchas situaciones, aun si queremos eliminar todo el contenido de la tabla, no podemos hacerlo.

Ahora bien, que es lo que hace TRUNCATE y porque es mas rápido que su “competidor”?
Para eso primero voy a hacer una brevísima introducción a como almacena internamente SQL Server los datos.
En SQL Server, los registros de una tabla, son agrupados en una estructura física de datos, que se llama página. Cada página tiene un tamaño fijo de 8060 bytes, y puede almacenar uno o cientos de registros, dependiendo del tamaño del mismo. Cuando se intenta insertar más registros en una tabla y la página de datos está llena, se crea otra donde se inserta el nuevo registro y así sucesivamente.
El comando TRUNCATE, lo que hace es desasociar (deallocate) las páginas de datos de la tabla, sin alterar los registros en sí mismo, mientras que el DELETE FROM recorre cada uno de los registros y los marca como borrados, por lo tanto, hace muchas más operaciones de I/O, que aumenta exponencialmente en relación con TRUNCATE, a medida que aumenta el tamaño de la tabla.
Otra razón que explica la diferencia de performance, es que TRUNCATE TABLE solo loguea en el transaction log, el deallocate de las paginas con la tabla (por lo tanto, es posible hacer un rollback de un TRUNCATE, cosa que muchos piensan que no), mientras que el DELETE FROM manda al transaction log todos los registros afectados, lo que es obviamente mucho más costoso a nivel recursos de I/O.
Por último, al hacer un TRUNCATE un lockeo sobre la tabla, a diferencia del DELETE FROM que hace un lockeo por pagina o registro, el consumo de memoria para almacenar los objetos lockeados es mucho menor.

Lo nuevo de SQL Server 2008: MERGE

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

Lo nuevo de SQL Server 2008: Grouping Sets

Una característica piola que tiene la nueva versión de SQL Server, es el nuevo operador GROUPING SETS, que ya existía en otros motores como Oracle hace tiempo (es un estándar ANSI 2006) y permite combinar consultas de agrupación distintas en una sola consulta. El operador GROUPING SETS es una extensión de la cláusula estándar GROUP BY.
Cuando no se requieren todas las agrupaciones posibles que se generan utilizando un operador ROLLUP o CUBE (que ya existían en SQL Server 2005), se debe utilizar GROUPING SETS para especificar sólo las agrupaciones que se deseen. O sea, gracias a GROUPING SETS obtenemos los niveles de agrupación deseados y además nos devuelve el subtotal para cada subconjunto de agrupación.
Podríamos decir que GROUPING SET es mas abarcativo y genérico que ROLLUP o CUBE. Generalmente consultas que usen este tipo de operadores están relacionadas con el análisis de datos, reporting y todo lo relacionado con el mundo BI.
Este nuevo operador no permite hacer nada nuevo que antes no se pudiese, solo simplifica y optimiza determinadas consultas, lo ya de por si, es un factor importante.
Veamos un ejemplo de donde utilizarlo:

Supongamos que tenemos la siguiente tabla de Clientes con datos ya cargados:

CREATE TABLE [dbo].[Client]
(

[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Country] [varchar](50) NULL,
[Zone] [varchar](50) NULL,
[Year] [int] NULL,
[Precio] [numeric](12, 4) NULL,

)

Y ahora supongamos que queremos traer la suma del campo Precio, agrupada por los Campos Country y Zone, luego solo por Zone y luego por el total y todo en una sola misma sentencia de tal manera que el resultado de la consulta fuese el siguiente:

La primera opción que uno piensa, es hacer una consulta usando UNION ALL con tres consultas diferentes, una por cada agrupación:

SELECT SUM(Value) AS Total, Zone, Country FROM dbo.Client
GROUP BY Zone,Country

UNION ALL
SELECT SUM(Value), NULL, Country FROM dbo.Client
GROUP BY Country

UNION ALL
SELECT SUM(Value), NULL, NULL FROM dbo.Client
ORDER BY Zone, Country

Sin embargo, podemos escribir una consulta equivalente y acá entra GROUPING SETS de SQL Server 2008 para simplificarnos la vida:

SELECT SUM(value) AS Total, Zone, Country FROM dbo.Client
GROUP BY
GROUPING SETS
(
(Zone, Country),
(Country),
()
)
ORDER BY GROUPING(Zone), GROUPING(Country)

Ahora como podrán ver en este último ejemplo, además de usar el operador GROUPING SETS, hago uso de la función GROUPING en el ORDER BY, para ordenar el resultado por Zona y Country.
Esta función (que ya existía en SQL Server 2005), nos indica si una expresión de columna especificada en una lista GROUP BY es agregada o no. GROUPING devuelve 1 para agregado y 0 para no agregado, en el conjunto de resultados.

Performance:

La ventaja del uso GROUPING SETS no está solo dada por simplificar sintácticamente las consultas, sino también en cuestiones de performance.
En las pruebas que hice para monitorear el uso de recursos (SET STATISTICS IO ON) con 90000 registros en la tabla Client estos fueron los resultados:

Usando UNION ALL:
Table ‘Client’. Scan count 3, logical reads 1728, physical reads 6, read-ahead reads 590, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Usando GROUPING SETS:
Table ‘Client’. Scan count 1, logical reads 576, physical reads 6, read-ahead reads 590, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Como podemos ver, GROUPING SETS hace menor uso de recursos de I/O. La razón de la mejor performance, se debe a que usando la nueva característica de SQL Server, el motor necesita leer menos paginas de datos, ya que hace el cálculo de agregación de más alto nivel sobre las agregaciones de menor nivel (usando como ejemplo nuestra consulta, el nivel de menor de agregación seria Zone y Country, luego solo Country y por último a partir del resultado de la prime).
Dejo la tarea al lector para que compare los planes de ejecución y pueda apreciar las diferencias en las operaciones que realiza el motor entre una consulta y otra.

Links:
Recomiendo leer el articulo de MSDN de equivalencias de GROUPING SETS.

Soluciones para actualizar un registro si existe, sino insertar en SQL Server.

Muchas veces cuando trabajamos con ABMs o algún proceso de escritura en la base de datos, al actualizar los registros, debemos establecer si vamos a efectuar un INSERT o un UPDATE. O sea, tenemos que determinar si el registro existe o no, para saber que operación se va a efectuar en la base de datos.

Generalmente se suele encapsular toda esta lógica dentro de un SP, algo que considero una muy buena practica, ya que nos desentendemos del lado de la aplicación, si se va a efectuar una operación de inserción o de modificación.

Primera solución:

Ahora bien, dentro del Store Procedure, lo solemos hacer para determinar la operación, es el famoso IF EXISTS.
Ejemplo:

IF EXISTS(SELECT ID FROM TABLA WHERE ID = @ID)
INSERT INTO TABLA (Campo1,ID) VALUES (@Valor,@ID)
ELSE
UPDATE TABLA SET Campo1 = @Valor WHERE ID = @ID

No es un mal enfoque, es muy claro. Sin embargo esta solución tiene dos inconvenientes:
1) Estamos pagando el costo de ejecutar un Query. Por mas que la consulta este indexada, tiene un costo.
2) No es 100% segura. En entornos muy demandantes, con alta concurrencia, puede darse el caso de que justo luego de ejecutar el IF EXISTS, otro proceso inserte en la tabla un registro con la misma PK y no tendríamos forma de darnos cuenta, generando un error de duplicate key.
Por lo tanto, esta opción que es la más común, tiene serios inconvenientes.

Segunda solución:

Una segunda opción podría ser esta:

UPDATE TABLA SET Campo1 = @Valor WHERE ID = @ID
IF @@ROWCOUNT = 0
INSERT INTO TABLA (Campo1,ID) VALUES (@Valor,@ID)

En caso, se eliminaría el tener que ejecutar una query con el EXISTS. Aunque en caso de que no exista el registro, se ejecuta el UPDATE innecesariamente.
Si la mayoría de las operaciones van a ser del tipo INSERT, en realidad no se ganaría performance, pero por el contrario, si la mayoría de las operaciones seria del tipo UPDATE, podría llegar a ser mas performante.
De todas maneras esta solución sigue teniendo el problema de que otro proceso podría insertar un registro con la misma PK en la tabla y no tendríamos forma de darnos cuenta.

Tercera solución:

En el segundo caso ganamos un poco de performance (no siempre), pero seguimos con el mismo problema de concurrencia.
Pero ahora veamos este ejemplo de código:

BEGIN TRY
INSERT INTO TABLA (Campo1,ID) VALUES (@Valor,@ID)
END TRY
BEGIN CATCH
UPDATE TABLA SET Campo1 = @Valor WHERE ID = @ID
END CATCH

A nivel perfomance, es similar a las otras soluciones, pero si tenemos muchas más operaciones de inserción que de actualización, vamos a ganar velocidad.
Sin embargo, en este caso no tendríamos el inconveniente de concurrencia que sucede en los 2 casos anteriores!!. Lo cual lo hace ideal para situaciones de alta demanda.

Cuarta solución (Solo en SQL Server 2008):

SQL Server 2008, incorpora el comando MERGE (que ya teníamos en Oracle y otros motores), que sirve para resolver de una manera muy eficiente, exactamente este problema.

MERGE TABLA
USING (SELECT @ID AS ID) AS SRC ON SRC.ID = TABLA.ID
WHEN MATCHED THEN
UPDATE SET Campo1 = @Valor
WHEN NOT MATCHED THEN
INSERT (Campo1,ID) VALUES (@Valor,@ID)

Con este método, también solucionamos el problema de concurrencia, y además evitar tener que ejecutar consultas innecesarias. Por lo cual, podríamos decir que es la optima solución resolver este problema, aunque lamentablemente debemos esperar hasta mitad de año, cuando Microsoft libere SQL Server 2008.

Conclusión:

Vimos como un problema en apariencia tonto y trivial, puede causar serios problemas de performance y peor aun, crear errores de concurrencia y comportamientos no deseados.
Por las pruebas que hicimos en un entorno de TEST, la diferencia de performance que hicimos no son demasiadas. Pero en situaciones de alta concurrencia, las 2 primeras soluciones son definitivamente incorrectas.

Recomiendo ver estos links, que explican como funcionan los lockeos, en cada una de las distintas soluciones:

http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx
http://weblogs.sqlteam.com/mladenp/archive/2007/08/03/60277.aspx

Y estas soluciones alternativas al mismo problema, tal vez sean un poco más complejas, pero en algunos escenarios pueden ser útiles:

http://www.samsaffron.com/blog/archive/2007/04/04/14.aspx
http://www.sqlteam.com/article/application-locks-or-mutexes-in-sql-server-2005