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. |