Semejanzas y diferencias entre DELETE, TRUNCATE y DROP TABLE

¡Hola lectores!
Todo está bien ?

Hoy vamos a hablar de una pregunta clásica que enfrentan los desarrolladores y algunos DBA que recién están comenzando. Después de todo, ¿cuál es la diferencia entre ELIMINAR, TRUNCAR TABLA y DROP TABLE? ¿Cuándo debemos utilizar cada uno de estos comandos?

Para ilustrar esta diferencia, presentaré la siguiente tabla:

	BORRAR	TRUNCAR	GOTA
Elimina datos de una tabla.
Es una operación DDL (Requiere privilegios para hacerlo)
Es una operación DML (requiere privilegios para hacerlo)
Requiere bloqueo exclusivo del objeto a ejecutar (ninguna otra sesión puede utilizar este objeto)
Bloquea el objeto a nivel de línea.
Bloquea el objeto a nivel de esquema.
Durante la ejecución del comando, otras sesiones pueden leer el objeto usando HINTS para lectura "sucia" (Ejemplo: NOLOCK)
Generalmente en tablas muy grandes y con mucho uso puede generar BLOQUEOS y contenciones notorias en la base de datos.
Le permite seleccionar qué datos se eliminarán (No elimina la tabla completa)
También elimina la estructura de la tabla, metadatos, índices, FK, PK. Elimina el objeto de la base de datos.
Reinicia el incremento automático, recrea los índices y desfragmenta la tabla.
Genera datos en el registro de transacciones/registro de rehacer (Oracle), lo que permite restaurar los datos mediante una copia de seguridad del registro.
Comandos extremadamente rápidos de ejecutar.
Es posible ejecutar el comando incluso si la tabla en cuestión es FK de otra tabla
Posibilidad de uso junto con Triggers.
Se puede utilizar en tablas que forman parte de vistas indexadas (MSSQL)

Algunos puntos relevantes

• El comando DROP TABLE elimina la tabla y su estructura. El objeto se eliminará de la base de datos. Este comando no solo borra los datos
• Los comandos DROP TABLE y TRUNCATE TABLE no generan registros detallados de operaciones. Sólo registran que el comando fue ejecutado y las páginas afectadas. Por lo tanto, ocupan muy poco espacio en el registro de transacciones/registro de rehacer y se ejecutan muy rápidamente. El lado malo de esto es que si un día necesita realizar una restauración inmediata utilizando el registro de la base de datos después de que alguien haya hecho TRUNCATE TABLE, esto no será posible. El comando DELETE registra cada fila que se eliminó, generando una gran cantidad de registros (debido a la cláusula WHERE), dependiendo del tamaño de la tabla. Esto le permite realizar una restauración inmediata después de que alguien haya realizado una ELIMINACIÓN incorrecta, pero puede hacer explotar su registro de transacciones/registro de rehacer si está eliminando una cantidad muy grande de registros.
• El comando TRUNCATE TABLE elimina TODOS los registros de una tabla. Además, reinicia el incremento automático (si lo hay), reduce la fragmentación de las tablas y los índices a 0, casi no genera registros y se ejecuta rápidamente en la base de datos, incluso con tablas muy grandes. Para rutinas donde todos los datos se borran y se generan nuevamente con cada ejecución, es la solución más recomendada.
• Dado que TRUNCATE simplemente elimina todas las páginas y extensiones de una tabla, no sería posible validar si alguna de las tablas secundarias hace referencia a alguno de estos registros. DELETE registra línea por línea y si no hay violación de la integridad referencial, se puede usar incluso en tablas referenciadas. La opción CASCADE es capaz de propagar actualizaciones a DELETE, pero no a TRUNCATE ya que este comando no mantiene la lista de líneas afectadas y por lo tanto no es capaz de propagar sus efectos.
• En Oracle Database, hay un activador que se activa en el evento "DESPUÉS DE TRUNCATE ON Database", que se puede usar después de un comando TRUNCATE, para registrar qué usuario ejecutó el comando, por ejemplo. Pero no existe un desencadenante específico antes de ejecutar el comando. Esto se puede crear usando un activador que se activa en el evento "ANTES de DDL EN la base de datos", pero no es una solución "oficial".
• Las vistas indexadas materializan datos de una tabla o de varias tablas combinadas. Si se ejecutara TRUNCATE en una tabla participante, la vista indexada simplemente dejaría de ser válida, ya que los comandos de eliminación individuales no se registrarían y una falla no permitiría que la base de datos se recuperara (no habría seguimiento de los cambios) para volver a indexar la vista.
• Como son operaciones diferentes (DML x DDL), los privilegios necesarios para ejecutar el comando DELETE son diferentes a los necesarios para DROP TABLE y TRUNCATE TABLE
• Los comandos DROP TABLE y TRUNCATE son prácticamente idénticos en todas las comparaciones. La única diferencia entre los dos es que DROP TABLE elimina objetos y metadatos de la base de datos, mientras que TRUNCATE TABLE simplemente deja la tabla vacía (sin registros).

Ejemplos de uso

/* Apagando a tabela "clientes" e seus dados, sua primary key, as foreign keys e índices */
DROP TABLE clientes

/* Apagando todos os dados da tabela "clientes" e reduzindo a fragmentação dos índices e tabelas para 0 */
TRUNCATE TABLE clientes

/* Apagando os dados de clientes que são do estado de São Paulo. Neste caso, cada linha apagada será logada 
e a fragmentação dos índices e tabelas não será alterado */
DELETE FROM clientes
WHERE UF = 'SP'

Espero haber despejado todas tus dudas sobre este tema.
Si aún tienes dudas, deja tu comentario abajo y las responderé 🙂