Similarities and Differences between DELETE, TRUNCATE and DROP TABLE

Hello readers!
Alright?

Today we are going to talk about a classic developer question and some DBA's that are starting now. 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
Erase 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 lock of object to be executed (No other session can be using this object)
Locks object at row level
Locks the schema level object
During the execution of the command, other sessions can take readings on the object using HINTS for “dirty” reading (Ex: NOLOCK)
Generally, in very large and heavily used tables, it can generate noticeable LOCKS and containments in the database.
Allows you to select which data to delete (Does not erase the entire table)
It also deletes the table structure, metadata, indexes, FK's, PK. Deletes the object from the database.
Restarts auto increment, rebuilds indexes, and defragments table
Generates data in transaction log / Redo log (Oracle), allowing data to be restored using log backup
Extremely fast commands to execute
You can execute the command even if the table in question is FK from another table
Possibility of use in conjunction with Triggers
Can be used on tables that are parts of indexed views (MSSQL).

Some relevant points

• The DROP TABLE command clears the table and its structure. The object will be deleted from the bank. This command not only erases data
• The DROP TABLE and TRUNCATE TABLE commands do not generate detailed logs of operations. They just record that the command was executed and the pages affected. Because of this, they take up very little space in the transaction log / redo log and run so quickly. The downside is that if you ever need to do an immediate restore using the bank log after someone has done a TRUNCATE TABLE, this will not be possible. The DELETE command logs each row that has been 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 made a wrong DELETE, but it may break your transaction log / redo log if you are deleting too many records.
• The TRUNCATE TABLE command erases ALL records in a table. In addition, it restarts auto-increment (if any), reduces table fragmentation and indexes for 0, almost does not log, and runs quickly in the database even with very large tables. For routines where all data is erased and regenerated at each execution, this is the most recommended solution.
• Since TRUNCATE simply deletes all pages and extensions from a table, it would not be possible to validate whether any of these records are referenced by any child tables. DELETE logs row by row and if there is no referential integrity violation it can be used even in referenced tables. The CASCADE option is able to propagate updates to DELETE, but not to TRUNCATE since this command does not maintain the affected row relationship and is therefore not able to propagate its effects.
• In Oracle Database, there is a trigger that is triggered in the event “AFTER TRUNCATE ON Database”, which can be used after some TRUNCATE command, to log which user has executed the command, for example. But there is no specific trigger for before the command is executed. This can be created using a trigger that is triggered in the “BEFORE DDL ON Database” event, but it is not an “official” solution
• Indexed views materialize data from a combined table or multiple tables. If TRUNCATE were executed on a participating table, the indexed view would simply be invalid, as individual delete commands would not be logged and a failure would not allow the bank to recover (no tracking changes) to redo the view index.
• Because these 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 virtually identical in all comparisons. The only difference between them is that DROP TABLE deletes the database objects and metadata, while TRUNCATE TABLE just leaves the table empty (no records).

Usage examples

I hope I have answered all your questions on this subject.
If you have any further questions, please leave your comment below and I will answer it 🙂