73

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:

Comparison Basis	DELETE	TRUNCATE
Definition	The delete statement is used to remove single or multiple records from an existing table depending on the specified condition.	The truncate command removes the complete data from an existing table but not the table itself. It preserves the table structure or schema.
Language	It is a DML (Data Manipulation Language) command.	It is a DDL (Data Definition Language) command.
WHERE	It can use the WHERE clause to filter any specific row or data from the table.	It does not use the WHERE clause to filter records from the table.
Permission	We need to have DELETE permission to use this command.	We need to have ALTER permission to use this command.
Working	This command eliminates records one by one.	This command deletes the entire data page containing the records.
Lock	It will lock the row before deletion.	It will lock the data page before deletion.
Table Identity	This command does not reset the table identity because it only deletes the data.	It always resets the table identity.
Transaction	It maintains transaction logs for each deleted record.	It does not maintain transaction logs for each deleted data page.
Speed	Its speed is slow because it maintained the log.	Its execution is fast because it deleted entire data at a time without maintaining transaction logs.
Trigger	This command can also activate the trigger applied on the table and causes them to fire.	This command does not activate the triggers applied on the table to fire.
Restore	It allows us to restore the deleted data by using the COMMIT or ROLLBACK statement.	We cannot restore the deleted data after using executing this command.
Indexed view	It can be used with indexed views.	It cannot be used with indexed views.
Space	The DELETE statement occupies more transaction space than truncate because it maintains a log for each deleted row.	The TRUNCATE statement occupies less transaction space because it maintains a transaction log for the entire data page instead of each row.

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.

---