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 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 ....
Great using Transcation log explian Properly.
ReplyDeleteThank you @Dinesh Vishe
DeleteThank you so much for this useful post, I have learned something new.
ReplyDelete@JG khan - Thanks a lot. Keep watching this site.
Delete