Cuando hablamos de bases de datos, uno de los temas que a menudo genera confusión es la diferencia entre las operaciones TRUNCATE y DELETE en SQL Server. Ambas sirven para eliminar datos, pero lo hacen de maneras fundamentalmente distintas, lo que las hace adecuadas para diferentes escenarios. Seguro que todos habéis oído hablar de que truncate es una operación que no se puede revertir, incluso habréis escuchado que no registra la operación en el log de transacciones. Bien pues eso, como suele pasar con todas estas cosas, tiene matices. Digamos que son verdades a medias que pueden servir para un usuario no tan avanzado pero que, si queremos ir más allá, tenemos que comprender a fondo.
Operaciones DDL y DML
Para entender las diferencias entre TRUNCATE y DELETE, lo primero que debemos comprender es las categorías a las que pertenecen: TRUNCATE es una operación DDL (Data Definition Language), mientras que DELETE es una operación DML (Data Manipulation Language). Las operaciones DDL, como CREATE, ALTER o DROP, se utilizan para definir o modificar la estructura de las bases de datos y sus objetos. Por el contrario, las operaciones DML, como INSERT, UPDATE y DELETE, se utilizan para manipular los datos que residen dentro de esas estructuras.
La clasificación de TRUNCATE como una operación DDL implica que no solo afecta los datos, sino también la estructura de la tabla de una manera fundamental. Al eliminar datos con TRUNCATE, no se eliminan registros individuales, sino que se vacía la tabla por completo, lo que va a tener un impacto distinto en el rendimiento y el uso de recursos del sistema, entre otras cosas.
TRUNCATE en SQL Server
La operación TRUNCATE en SQL Server es rápida y eficiente cuando se necesita eliminar todos los datos de una tabla. La teoría dice que, a diferencia de DELETE, que borra registros fila por fila y puede generar una gran cantidad de entradas en el log de transacciones, TRUNCATE simplemente resetea las páginas de datos, liberando todo el espacio asociado a los registros en un solo paso. Entendamos esto.
Si recordáis, cuando hablamos de las estructuras físicas de los datos, os conté que SQL Server almacena los registros en páginas de 8 Kbs. El tamaño de la página en sí no es importante aquí pero si tenemos que entender que estas páginas son exclusivas para una tabla, es decir una tabla puede tener sus datos en varias páginas pero cada página solo va a almacenar datos de una tabla.
Bien, pues con esto en mente, ya podemos entender lo que acabamos de decir, cuando ejecutamos un TRUNCATE, lo que hace el motor de base de datos es buscar todas las páginas de datos de esa tabla y, sin importar lo que haya dentro, eliminarlas directamente. También va a actuar sobre una serie de metadatos en tablas de sistema pero eso lo veremos más adelante, no nos compliquemos ahora. Este comportamiento no solo reduce el uso de log de transacciones, sino que también hace que la operación sea considerablemente más rápida.
Otras consideraciones de TRUNCATE en SQL Server
Otra característica clave de TRUNCATE es que no activa los triggers de la tabla, dado que no se considera una operación sobre registros individuales como ya acabamos de explicar. Sin embargo, hay una excepción importante a esto, aunque TRUNCATE elimina todos los registros, no puede ser utilizado si existen restricciones de integridad referencial, como claves foráneas (Foreign Keys). Para utilizar TRUNCATE en estos casos, primero se deben eliminar o deshabilitar las restricciones referenciales.
DELETE en SQL Server
Por otro lado, la operación DELETE es más versátil, ya que permite eliminar registros de forma selectiva utilizando una cláusula WHERE. Esto la convierte en la mejor opción cuando necesitamos eliminar solo una parte de los datos de una tabla. Además, DELETE es una operación completamente registrada, lo que significa que cada eliminación se registra en el log de transacciones (fila a fila), permitiendo una recuperación granular de los datos si fuese necesario.
Sin embargo, esta granularidad tiene un coste en términos de rendimiento. Al registrar cada eliminación de forma individual, DELETE puede ser significativamente más lento que TRUNCATE cuando se trata de eliminar grandes volúmenes de datos. Además, DELETE activa triggers y respeta las restricciones de integridad referencial, lo que puede añadir complejidad adicional a la operación.
TRUNCATE y DELETE con Campos IDENTITY y Secuencias
Un aspecto crítico al utilizar TRUNCATE y DELETE es cómo cada una de estas operaciones afectan los campos autoincrementales y las secuencias.
Campos IDENTITY
En SQL Server, los campos autoincrementales, conocidos como campos IDENTITY, generan un valor único y creciente para cada nuevo registro. Cuando se utiliza DELETE para eliminar registros, el valor actual de IDENTITY no se ve afectado. Esto significa que, después de una operación de DELETE, el próximo registro insertado continuará con el siguiente valor de IDENTITY, sin importar cuántos registros hayan sido eliminados. Por ejemplo, si el último valor fue 100, el próximo registro será 101, incluso si todos los registros anteriores fueron eliminados.
En cambio, cuando se utiliza TRUNCATE, el comportamiento es diferente. TRUNCATE reinicia el valor de IDENTITY a su valor inicial, generalmente 1. Esto ocurre porque TRUNCATE no solo elimina todos los registros, sino que también resetea el estado interno de la tabla, incluyendo los contadores incrementales (una de esas operaciones de metadatos de las que hablábamos antes). Esta diferencia es crucial en escenarios donde el mantenimiento del orden de IDENTITY es importante.
Secuencias
Las secuencias en SQL Server, a diferencia de los campos IDENTITY, son objetos independientes que generan valores únicos secuenciales que pueden ser utilizados en múltiples tablas. Cuando se utiliza DELETE o TRUNCATE, las secuencias no se ven afectadas directamente, ya que el valor generado por una secuencia no está ligado al contenido de una tabla específica. Por esta razón, si queremos reiniciar una secuencia, esto debe hacerse explícitamente utilizando una instrucción ALTER SEQUENCE.
Permisos Necesarios para TRUNCATE y DELETE
Otra diferencia fundamental entre TRUNCATE y DELETE es el nivel de permisos requerido para ejecutar cada operación.
Para ejecutar una operación DELETE, un usuario necesita permisos de DELETE en la tabla en cuestión. Estos permisos son relativamente comunes y se pueden otorgar a través de roles estándar como db_datawriter o directamente a nivel de tabla. Dado que DELETE es una operación DML, no requiere permisos adicionales sobre la estructura de la tabla ni afecta los metadatos de la base de datos.
Por otro lado, TRUNCATE es una operación DDL, lo que significa que requiere permisos más elevados. Para ejecutar TRUNCATE, el usuario necesita permisos de ALTER en la tabla, ya que la operación afecta tanto la estructura de la tabla como los datos. Además, si la tabla está involucrada en restricciones de integridad referencial, el usuario también necesitará permisos adicionales para manejar esas relaciones. Tenemos que tener muy en cuenta este requisito de permisos más elevados en entornos donde deseamos limitar el acceso a operaciones que pueden alterar significativamente la estructura de la base de datos.
Rollback en TRUNCATE y DELETE
Llegamos a lo que todos estabais esperando, la capacidad de hacer rollback y deshacer una transacción de borrado. Como hemos visto hasta ahora, DELETE va a registrar cada una de las filas borradas como una operación en el log de transacciones por lo que podemos revertir la operación, teóricamente incluso parcialmente. Pero, ¿y qué pasa con TRUNCATE? ¿Es cierto que no se puede hacer ROLLBACK? Seguramente creas que no se puede, porque eso es lo que te han contado. Pero esa afirmación viene de una inexactitud que quiero aclarar aquí y ahora. Para ello abre un poco tu mente que voy a contarte esos matices de los que hablaba en la introducción. Mira la siguiente captura:
¿Qué ha pasado aquí? Esto no era lo que te habían explicado. He creado una tabla e insertado tres registros. Luego en una transacción he hecho un TRUNCATE y la tabla se ha borrado pero, cuando he hecho un ROLLBACK, los datos seguían ahí. Lo primero que tenemos que entender en este punto es como SQL Server gestiona las transacciones, esto es algo que explicamos aquí, por lo que si no lo has leído y el próximo párrafo te suena a chino te recomiendo ir al enlace en este punto y aclarar esos conceptos.
¿Cuándo no se puede hacer ROLLBACK de un TRUNCATE?
TRUNCATE, aunque no registra fila a fila los borrados, sí que deja rastro en el log, mínimo pero deja rastro. Concretamente registra las páginas que han sido borradas. Incluso las bloquea hasta que la transacción se confirma. Esto quiere decir que si se puede hacer ROLLBACK de un TRUNCATE siempre que la transacción no esté confirmada, es decir, dentro de una transacción implícita o explícita, antes de hacer COMMIT. Gracias a registrar y bloquear las páginas borradas (impedir su borrado o sobrescritura) mientras no haya un COMMIT es posible volver a restaurarlas en caso de ROLLBACK.
Si ahora estás pensando que nada tiene sentido y que te han engañado, tranquilo, no es del todo así. Realmente, en parte, sí que es cierta la afirmación de que no se puede hacer ROLLBACK de un TRUNCATE. Lo que pasa es que solo aplica a deshacer una transacción desde el fichero de log, es decir a restaurar la base de datos a un punto en el tiempo previo a una transacción.
TRUNCATE en Diferentes Sistemas Gestores de Bases de Datos
Ahora que hemos visto cómo funcionan TRUNCATE y DELETE en SQL Server, es interesante analizar cómo TRUNCATE se comporta en otros sistemas gestores de bases de datos.
Truncate en Oracle
En Oracle, la operación TRUNCATE funciona de manera muy similar a SQL Server. Es una operación DDL que vacía una tabla de forma rápida y eficiente, sin generar una cantidad significativa de entradas en el log de transacciones. Como en SQL Server, TRUNCATE no puede ser utilizado si la tabla tiene claves foráneas activas, lo que exige deshabilitar estas restricciones antes de ejecutar la operación. Sin embargo, Oracle permite la opción de TRUNCATE … CASCADE, que automáticamente trunca las tablas relacionadas que dependan de la tabla principal.
Truncate en MySQL/MariaDB
En MySQL y MariaDB, TRUNCATE también se considera una operación DDL, aunque internamente se ejecuta como un DROP TABLE seguido de un CREATE TABLE para recrear la estructura de la tabla. Este enfoque significa que el rendimiento es similar al de otros sistemas, con la ventaja añadida de que libera de inmediato el espacio de almacenamiento utilizado por la tabla. Por el contrario, este planteamiento también significa que es una operación 100% irreversible, no como lo que acabamos de ver en SQL Server. Además, a diferencia de SQL Server y Oracle, MySQL no permite truncar una tabla si existen tablas que dependen de ella a través de claves foráneas, incluso si esas claves están deshabilitadas.
Truncate en PostgreSQL
En PostgreSQL, TRUNCATE sigue siendo una operación DDL, pero con algunas características adicionales. Además de ser rápido y eficiente, PostgreSQL permite truncar múltiples tablas en una sola operación, lo que puede ser útil en escenarios donde se necesita vaciar varias tablas relacionadas de una sola vez. También incluye la opción CASCADE, que automáticamente trunca todas las tablas relacionadas. Al igual que en otros sistemas, TRUNCATE no activa triggers, lo que lo hace muy útil para operaciones de mantenimiento de bases de datos.
Truncate en Microsoft Fabric
En el caso de Fabric Datawarehouse, un sistema optimizado para grandes volúmenes de datos y cargas de trabajo analíticas, os iba a decir que TRUNCATE no es una operación permitida. Sin embargo, en los últimos días esto ha cambiado y ahora sí que es posible hacerlo. Aunque es una característica realmente nueva de la que aún hay poca información, TRUNCATE en los warehouse de Fabric se comporta de manera similar a otros sistemas, pero con algunas consideraciones adicionales.
Dado que Fabric Datawarehouse está diseñado para gestionar grandes cantidades de datos de manera eficiente, TRUNCATE es especialmente útil para operaciones de limpieza y reinicialización de tablas. Sin embargo, no pierde la esencia de un almacén de datos por lo que, lo que TRUNCATE, es este caso, es una operación sólo de metadatos. Escribe un nuevo registro delta eliminando todos los archivos parquet existentes. De esta manera es mucho más rápido que DELETE, pero preserva la historia de la tabla, a diferencia de CTAS/DROP/RENAME.
Conclusión
Aunque TRUNCATE registra menos información en el log de transacciones que DELETE, registra lo suficiente para permitirnos un rollback completo dentro de una transacción implícita o explícita. Este equilibrio entre un registro mínimo y la capacidad de revertir la operación hace de TRUNCATE una opción muy interesante para eliminar rápidamente grandes volúmenes de datos. Sin embargo, al usarlo debemos extremar las precauciones, ya que la reversión es todo o nada, y siempre debemos tener en cuenta que los permisos necesarios son más elevados.
En resumen, TRUNCATE ofrece una opción rápida y efectiva para la limpieza de tablas, siempre que comprendamos sus implicaciones y limitaciones en el contexto de transacciones y logs. Si la prioridad es el rendimiento y se necesita vaciar una tabla por completo sin preocuparse por las restricciones de integridad referencial, TRUNCATE es la opción más adecuada. En cambio, si se requiere eliminar registros selectivos o si existen dependencias de claves foráneas que no pueden ser deshabilitadas, DELETE será la operación preferida.
Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!
No te vayas aun. Hemos creado una página donde estamos recopilando todos estos artículos que dan respuesta a estas preguntas frecuentes de SQL Server. Pásate por aquí a echar un vistazo.
