Difference between DELETE and TRUNCATE

The main difference between TRUNCATE and DELETE lies in their approach to removing data from a database table. While both operations serve to eliminate records, DELETE selectively removes specific rows based on conditions, preserving table structure and constraints. In contrast, TRUNCATE swiftly removes all data from a table, resetting it to an empty state without considering individual rows. This speed advantage comes at the cost of irreversibility and the resetting of identity columns or sequences, making it crucial to choose the appropriate operation based on the desired outcome and database management requirements.

What is a DELETE Command?

DELETE is a DML (Data Manipulation Language) command that is used to delete rows/tuples from a specified table or relation in SQL. Any number of rows can be removed using the DELETE command. It is mostly combined with the WHERE clause to delete some specific rows which satisfy the condition. If the WHERE clause is not added to the query, the DELETE command removes all the rows and empties the table. Delete permissions on the table are required to use this command.

The DELETE statement builds a log file in the transaction log, which indicates it stores the records before deleting them. Hence, in case some important rows of data are removed using the DELETE command, they can be retrieved back using the ROLLBACK command. Since DELETE is a DML command, changes must be manually committed. Therefore, ROLLBACK must be done before COMMIT.

Syntax:

Example Query 1:

Output:

Use the following query to delete all the rows from the student's table.

Output:

Here, all the records are deleted from the table students.

Example Query 2:

Here, only those records are removed from sales where id matches with 2. Hence, merging DELETE and WHERE commands help to filter out the data in SQL.

What is a TRUNCATE Command?

TRUNCATE is a DDL (Data Definition Language) command used to remove complete data from the existing table without modifying or deleting the table structure. WHERE clause cannot be used with TRUNCATE, so we cannot do conditional removal of data from the table using TRUNCATE. Hence, it is better to use it where the entire data of a particular table is to be removed.

Once we delete the data using the TRUNCATE command, the data cannot be retrieved using the ROLLBACK statement. Hence this command must be used carefully as it can lead to the loss of important records. TRUNCATE command deallocates the data pages instead of rows and keeps track of deleted pages in place of rows in transaction logs. This feature of TRUNCATE makes it much faster than DELETE.

Syntax:

Example Query:

Output:

The above statement removed all the records from the sales table in one go.

It's essential to grasp the difference between truncate and delete. While both commands serve the purpose of removing data from a table, they differ significantly in their implications and usage scenarios.

Key differences between DELETE and TRUNCATE

1. The DELETE statement removes some or all records from the table, while TRUNCATE deletes entire rows from a table.
2. DELETE is a DML command, modifying table data, while TRUNCATE is a DDL command.
3. DELETE allows filtering using the WHERE clause, whereas TRUNCATE does not support WHERE clause filtering.
4. DELETE triggers all delete triggers on the table, whereas no triggers are activated during TRUNCATE as it doesn't operate on individual rows.
5. DELETE performs row-by-row deletion, whereas TRUNCATE operates on data pages, deleting entire table data at once.
6. DELETE does not reset table identity, while TRUNCATE resets the identity.
7. DELETE requires more locks and database resources due to acquiring locks on each deleted row, while TRUNCATE requires fewer locks by acquiring locks on data pages.
8. DELETE makes an entry in the transaction log for each deleted row, while TRUNCATE records transaction logs for each data page.
9. TRUNCATE is faster than DELETE as it deallocates data pages instead of rows and records data pages instead of rows in transaction logs.
10. Records deleted by TRUNCATE cannot be recovered, whereas data removed by DELETE can be recovered.
11. DELETE operations can be part of a transaction and rolled back, while TRUNCATE is auto-committed and cannot be undone.
12. DELETE maintains referential integrity by enforcing constraints, whereas TRUNCATE may violate constraints since it removes all data indiscriminately.
13. DELETE offers finer control over recovery points, allowing for point-in-time recovery, whereas TRUNCATE lacks this granularity.
14. DELETE operations may increase recovery time due to extensive logging, while TRUNCATE speeds up recovery by minimizing logged operations.
15. After DELETE, the table retains its original size, but after TRUNCATE, the table size decreases significantly.

DELETE vs TRUNCATE

Let's delve deeper into the difference between truncate and delete to understand their distinct functionalities and implications for database management: