Difference between Delete and Truncate Command

The difference between DELETE and TRUNCATE command is the most common part of an interview question. They are mainly used to delete data from the database. The main difference between them is that the delete statement deletes data without resetting a table's identity, whereas the truncate command resets a particular table's identity. This article explains the complete overview of DELETE and TRUNCATE command and their differences that are primarily used interchangeably but are totally different.

What is a DELETE command?

It is a DML or data manipulation command used to deletes records from a table that is not required in the database. It removes the complete row from the table and produces the count of deleted rows. We need the delete permission on the target table to execute this command. It also allows us to filter and delete any specific records using the WHERE clause from the table.

It clarifies that we have a backup of our database before executing this command because we cannot recover the deleted records using this query. Therefore, the database backups allow us to restore the data whenever we need it in the future.

The following syntax explains the DELETE command to remove data from the table:

What is a TRUNCATE command?

The truncate statement is a DDL or data definition language command used to removes complete data from the table without removing the table structure. We cannot use the WHERE clause with this command, so that filtering of records is not possible. After executing this command, we cannot rollback the deleted data because the log is not maintained while performing this operation.

The truncate command deallocates the pages instead of rows and makes an entry for the deallocating pages instead of rows in transaction logs. This command locks the pages instead of rows; thus, it requires fewer locks and resources. Note that we cannot use the truncate statement when a table is referenced by a foreign key or participates in an indexed view.

The following syntax explains the TRUNCATE command to remove data from the table:

Key differences between DELETE and TRUNCATE

The following points explain the differences between delete and truncate command:

1. The DELETE statement is used when we want to remove some or all of the records from the table, while the TRUNCATE statement will delete entire rows from a table.
2. DELETE is a DML command as it only modifies the table data, whereas the TRUNCATE is a DDL command.
3. DELETE command can filter the record/tuples by using the WHERE clause. However, the TRUNCATE command does not allow to use WHERE clause, so we cannot filter rows while truncating.
4. DELETE activates all delete triggers on the table to fire. However, no triggers are fired on the truncate operation because it does not operate on individual rows.
5. DELETE performs deletion row-by-row one at a time from the table, in the order, they were processed. However, TRUNCATE operates on data pages instead of rows because it deleted entire table data at a time.
6. DELETE statement only deletes records and does not reset the table's identity, whereas TRUNCATE resets the identity of a particular table.
7. DELETE command require more locks and database resources because it acquires the lock on every deleted row. In contrast, TRUNCATE acquires the lock on the data page before deleting the data page; thus, it requires fewer locks and few resources.
8. DELETE statement makes an entry in the transaction log for each deleted row whereas, TRUNCATE records the transaction log for each data page.
9. TRUNCATE command is faster than the DELETE command as it deallocates the data pages instead of rows and records data pages instead of rows in transaction logs.
10. Once the record deletes by using the TRUNCATE command, we cannot recover it back. In contrast, we can recover the deleted data back which we removed from the DELETE operation.

DELETE vs. TRUNCATE Comparison Chart

The following comparison chart explains their main differences in a quick manner:

Conclusion

In this article, we have made a comparison between delete and truncate statement. We have concluded that a DELETE command is used when we want to customize the deletion of records from the table. And a TRUNCATE command is used when we do not want to left any records or data in the table, i.e., we want to empty the table.