Difference between delete and truncate commands

In this programming tutorial we will discuss the difference between delete and truncate commands. This is also a very important question asked in interview. The purpose of both delete and truncate is to clear the data but off course there are some differences due to them they both exists in ms sql server. So let's start.

Difference between delete and truncate commands

Delete

1. Identity column value is not reset on DELETE operation.
2. We can query with DELETE operation, such as ,

   delete * from users where user_name like '%a%'

3. As logging is done for each row affected, deleting is a slow process. As when we execute 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 a very large extent, this problem is more visible when you have to delete large amount of data in a table.
4. We can have triggers associated with DELETE operation.
5. We can delete one row or all the rows of the the table.

Truncate

1. Identity column reset on TRUNCATE operation
2. We cannot have query with TRUNCATE operation
3. As logging is done only when pages are de-allocated. It is faster than DELETE operation. This is because 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 actually the whole data pages is de-allocated and pointers to the indexes are removed. As such data remain there until it is over written by new data. At this point you might be thinking that TRUNCATE operation is not logged,but this is not the fact. In case of TRUNCATE operation the de-allocation of pages is logged in the log file.
4. We cannot have triggers associated with TRUNCATE operation
5. We can only TRUNCATE the whole table, means we can remove all the rows of the table, if rows exist. We cannot remove single row.

So that's it. These are the differences between delete and truncate commands.

I hope you will find this tutorial very handy.

I love your feedback.