DELETE and TRUNCATE in SQL

DELETE and TRUNCATE are the commands use to remove tuples from a relation, but they differ in many contexts. In SQL, DELETE command is a Data Manipulation Language command whereas, TRUNCATE command is a  Data Definition Language command.

However, the point that allows us to differentiate between DELETE and TRUNCATE is that DELETE is able to remove specified tuples from a relation, Whereas, the TRUNCATE command removes entire tuples from a relation.

We should not stop here, there are many other differences between DELETE and TRUNCATE. Let us discuss them with the help of comparison chart shown below.

Comparison Chart

Basis for Comparsion	DELETE	TRUNCATE
Basic	You can specify the tuple that you want to delete.	It deletes all the tuples from a relation.
Language	DELETE is a Data Manipulation Language command.	TRUNCATE is a Data Definition Language command.
WHERE	DELETE command can have WHERE clause.	TRUNCATE command do not have WHERE clause.
Trigger	DELETE command activates the trigger applied on the table and causes them to fire.	TRUNCATE command does not activate the triggers to fire.
Deletion	DELETE command eliminate the tuples one-by-one.	TRUNCATE delete the entire data page containing the tuples.
Lock	DELETE command lock the row/tuple before deleteing it.	TRUNCATE command lock data page before deleteing table data.
Speed	DELETE command acts slower as compared to TRUNCATE.	TRUNCATE is faster as compared to DELETE.
Transaction	DELETE records transaction log for each deleted tuple.	TRUNCATE record transaction log for each deleted data page.
Restore	DELETE command can be followed either by COMMIT or ROLLBACK.	TRUNCATE command can't be ROLLBACK.

Definition of DELETE

DELETE is a Data Manipulation Language (DML) command. The task of DELETE command is to delete or remove the tuples from a table or a relation. Using DELETE, we are able to eliminate the entire tuple, with all its attribute values from a relation. DELETE does not remove the value of a particular attribute, of a tuple from the relation.

You can filter the tuples that you want to delete from a table, using WHERE clause. If you specify WHERE clause in the DELETE statement, then it will only delete the tuples satisfying WHERE clause condition. But if you don’t specify the WHERE clause in the DELETE statement then by default it deletes or eliminates all the tuples from a relation. WHERE clause in DELETE  statement can contain nested SELECT-FROM-WHERE statements.

The syntax of DELETE  command is as follow:

DELETE FROM table_name WHERE [conditon] ;

DELETE command operates only on a single relation or table at a time. In case you want to delete tuple from different relations you have to place different DELETE command for each. But deleting tuple from one relation may violate the referential integrity of the database, which can be resolved with the help of triggers. If DELETE command violates referential integrity, then all referential triggers (if specified) gets activated that propagates the delete action on the tuples of different relation that refers to the deleted tuples.

DELETE command first lock the row or tuple to be deleted and then delete the tuple one-by-one. Hence, it requires more locks and resources which make it slower. You can make the changes done by DELETE statement permanent using COMMIT, or you can reset the database using ROLLBACK.

Definition of TRUNCATE

TRUNCATE is similar to DELETE command as it also deletes tuples from a relation. The difference is that it deletes entire tuples from a relation. As the TRUNCATE command is executed, entire data from the table is deleted, each tuple along with all it’s attribute values get eliminated from the table. But the structure of table still exists in the database. So, you can again enter the tuples in the table. TRUNCATE is a Data Definition Language command.

The syntax of TRUNCATE command is as follow:

TRUNCATE TABLE table_name;

Like DELETE, TRUNCATE does not operate on table data row-by-row. Instead, it operates on the data pages that store the table data. Now, when TRUNCATE delete data pages it has to acquire the lock on data pages instead of tuples. Hence, it results in less requirement of locks and resource that makes TRUNCATE faster as compared to DELETE.

Execution of TRUNCATE command does not activate any triggers since it does not operate data row-by-row. TRUNCATE can not be executed in case the table is referenced by any foreign key. Once TRUNCATE command deletes the table data, it can never be recovered back.

Key Differences Between DELETE and TRUNCATE in SQL

1. The main difference between DELETE and TRUNCATE is that using DELETE you can delete specified tuple from a relation. But the use of TRUNCATE will delete entire tuples from a relation.
2. DELETE is DML command whereas, TRUNCATE is DDL command.
3. DELETE uses WHERE clause to filter the record/tuples that are to be deleted. However, TRUNCATE does not require WHERE clause as it deletes all tuples, so no need to filter the tuples.
4. DELETE activates referential triggers applied to the tables. But TRUNCATE does not fire any triggers on the table.
5. DELETE command eliminates the tuples one-by-one from the table, in the order, they are processed. However, TRUNCATE does not operate on tuples one-by-one. Instead, TRUNCATE operates on data page that stores table data.
6. DELETE acquire the lock on the tuple before deleting it whereas, TRUNCATE acquire the lock on the data page before deleting data page.
7. TRUNCATE is faster as compared to DELETE command.
8. DELETE record transaction log for each tuple whereas, TRUNCATE record transaction log for each data page.
9. Once you delete data using TRUNCATE, it can never be recovered back whereas, you can recover your data back which you deleted using DELETE command.

Conclusion

If you want to customise the deletion of records from the table then you can use DELETE command. If you want to empty the table i.e. you don’t want to left any data in a table then use TRUNCATE command.