Similarities and Differences Between DELETE, TRUNCATE and DROP TABLE

Hello, readers!
All good ?

Today we’re going to talk about a classic question faced by developers and some DBA’s who are just starting out. After all, what is the difference between DELETE, TRUNCATE TABLE and DROP TABLE? When should we use each of these commands?

To illustrate this difference, I will present the table below:

	DELETE	TRUNCATE	DROP
Deletes data from a table
It is a DDL operation (Requiring privileges to do so)
It is a DML operation (Requiring privileges to do so)
Requires exclusive locking of the object to be executed (No other session can be using this object)
Locks the object at line level
Locks the object at the schema level
During command execution, other sessions can read the object using HINTS for “dirty” reading (Ex: NOLOCK)
Generally, in very large tables with a lot of use, it can generate noticeable LOCKS and contentions in the database
Allows you to select which data will be deleted (Does not delete the entire table)
It also deletes the table structure, metadata, indexes, FK’s, PK. Deletes the object from the database.
Restarts auto increment, recreates the indexes and defragments the table
Generates data in transaction log / Redo log (Oracle), allowing data to be restored using log backup
Extremely quick commands to execute
It is possible to execute the command even if the table in question is FK of another table
Possibility of use in conjunction with Triggers
Can be used on tables that are part of indexed views (MSSQL)

Some relevant points

• The DROP TABLE command deletes the table and its structure. The object will be deleted from the database. This command doesn't just erase the data
• The DROP TABLE and TRUNCATE TABLE commands do not generate detailed logs of operations. They only record that the command was executed and the pages affected. Therefore, they take up very little space in the transaction log / redo log and run so quickly. The bad side of this is that if one day you need to do an immediate restore using the database log after someone has done a TRUNCATE TABLE, this will not be possible. The DELETE command logs each row that was deleted, generating a very large number of log records (Due to the WHERE clause), depending on the size of the table. This allows you to perform an immediate restore after someone has performed a wrong DELETE, but it can blow up your transaction log / redo log if you are deleting a very large amount of records
• The TRUNCATE TABLE command deletes ALL records from a table. Furthermore, it restarts auto increment (if any), reduces table fragmentation and indexes to 0, generates almost no logs and runs quickly on the database, even with very large tables. For routines where all data is erased and generated again with each execution, it is the most recommended solution
• Since TRUNCATE simply deletes all pages and extents from a table, it would not be possible to validate whether any of these records are referenced by any child tables. DELETE logs line by line and if there is no violation of referential integrity, it can be used even on referenced tables. The CASCADE option is capable of propagating updates to DELETE, but not to TRUNCATE since this command does not maintain the list of affected lines and is therefore not capable of propagating its effects
• In Oracle Database, there is a trigger that is triggered in the “AFTER TRUNCATE ON Database” event, which can be used after a TRUNCATE command, to log which user executed the command, for example. But there is no specific trigger before executing the command. This can be created using a trigger that is fired in the “BEFORE DDL ON Database” event, but it is not an “official” solution
• Indexed views materialize data from a table or multiple tables combined. If TRUNCATE were executed on a participating table, the indexed view would simply become invalid, as individual delete commands would not be logged and a failure would not allow the database to recover (there would be no tracking of changes) to reindex the view
• As they are different operations (DML x DDL), the privileges required to execute the DELETE command are different from those required for DROP TABLE and TRUNCATE TABLE
• The DROP TABLE and TRUNCATE commands are practically identical in all comparisons. The only difference between the two is that DROP TABLE deletes objects and metadata from the database, while TRUNCATE TABLE just leaves the table empty (without records)

Examples of use

/* 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'

I hope I have cleared all your doubts on this subject.
If you still have questions, leave your comment below and I will answer them 🙂