Thursday, April 14, 2011

Difference between delete and truncate

I have attended many interviews in my 4 years of experience and all time this questions asked to me by interviewer. Many times this would be turning point of interview. so thought to give clear idea to user.

Difference between delete and truncate

Delete
Truncate
1. It maintains transaction logs.
1. It does not maintain transaction logs.
2. So It can be rollback.
2. It cannot be rollback.
3. We can write where condition with delete clause.
3.  Truncate does not allow where condition.
4. It does not reset identity column.
4. It resets identity column.
5. Activates triggers.
5. Does not activate triggers.


According to me all the above points are clear expect point number 4.

Explanation for point 4:

Suppose we have table name “Employee” and it consist of 5 rows.

Case 1) Using delete command deletes all the row and insert again new row to table it will start from identity 6. It means it does not reset identity column.

Case 2) using truncate command truncates all the rows and again inserts one new row it will start again from 1 instead of 6. So it means that truncate command reset identity column.

If any one knows better explanation please suggest ....