Difference between DELETE and TRUNCATE

We often come across the DELETE and TRUNCATE keyword in context of SQL and also know that it is used to clear the data from the table.

In this article I will try to dig in a little more and try figure out the difference between delete and truncate, performance of the two and scenarios for their usage.

DELETE:

When we fire a delete command on a table, log is generated for each row affected in the transaction log. Recording the log for each row, not only slow down the performance but it also increase the size of  transaction file to large extent, this issue is more visible when you have to delete large amount of data.

TRUNCATE

Truncate operation is faster than delete operation. This is because in case of TRUNCATE logging is not done for each row affected as a result of the operation. When truncate operation is performed on the table, data is not removed, but the whole data pages is de-allocated and pointers to the indexes are removed. As such data remain there till it is over written by some other data. At this point you might think that TRUNCATE operation is not logged, but this is not the case. In case of TRUNCATE operation the de-allocation of pages is logged in the log file. Best part is that we can use TRUNCATE within transaction block, so if something goes wrong in the middle of operation you can always rollback.

Now let me put down the other difference in the tabular format to make it easy for the guys preparing for interview to read and remember :-)

Sr. No	DELETE	TRUNCATE
1	Identity column value is not reset on DELETE operation	Identity Column reset on TRUNCATE operation
2	We can have query with DELETE operation DELETE FROM EMPLOYEE WHERE FNAMELIKE '%SA%'	We cannot have query with TRUNCATE operation
3	As logging is done for each row affected, this is slow process	As logging is done only when pages are de-allocated. It is faster than DELETE operation
4	We can have triggers associated with DELETE operation	We cannot have triggers associated with TRUNCATE operation
5	We can delete complete or part of the table data	We can either TRUNCATE the whole table  data or no table data