The blog has moved to the new site F10Debug.com

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 in SQL Server


Delete
Truncate
1. DML Statement.
1. DDL Statement.
2. It maintains transaction logs.
2. It does not maintain transaction logs.
3. So It can be rollback.
3. It cannot be rollback.
4. We can write where condition with delete clause.
4.  Truncate does not allow where condition.
5. It does not reset identity column.
5. It resets identity column.
6. Activates triggers.
6. Does not activate triggers.
7. Does provide no of rows affected.
7. It does not give no of rows affected.
8. If you need to delete the entire records of the Table, is pretty recommended to use the Truncate instruction, otherwise you can obtain time out error for example.

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

Explanation for point 5:

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

Case Delete) 
If we delete all the records of table, and enters new row into table it will start from 6 instead of 1. It means it does not reset identity column.

Case Truncate) 
If we truncate table, and enters new row into table it will start from 1 again. So it means that truncate command resets identity column.


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

4 comments:

  1. Great using Transcation log explian Properly.

    ReplyDelete
  2. Thank you so much for this useful post, I have learned something new.

    ReplyDelete
    Replies
    1. @JG khan - Thanks a lot. Keep watching this site.

      Delete