Similarities and Differences between DELETE, TRUNCATE and DROP TABLE

Views: 3.189
Reading Time: 6 minutes

Hello readers!
Alright?

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

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 fires on the “AFTER TRUNCATE ON Database” event, which can be used after some TRUNCATE command, to log which user 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 fires on 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 🙂