All

Delete vs Truncate

There is always a confusion about delete and truncate specially when this question comes suddenly in interview.

Most of the time,  the simple answer is that there are logs created in Delete and no logs in case of truncate. But that is also not fully rue. The correct answer is : In case of delete, as number of log entries are created as number of records being deleted means one log per record deleted. In case of truncate, one log entry is created for the whole truncate.

Some more differences along with above main one can be listed as :

  • One Log entry for the whole transaction and one log per record deleted in case of delete.
  • Truncate deletes all records, but you can put where clause in case of delete.
  • The Identity column counter value resets to 1 in case of Truncate, in case of delete it stays to the last inserted record
  • Delete can be rolled back, truncate cannot. In other words, truncate is committed transaction.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.