Difference between Delete and Truncate in sql server. Was I wrong...?

Question

In a recent interview I was asked the difference between the two. I replied the general answere that all we know...

The interviewer then asked if truncate can be rollbacked ? I replied no...

The interviewer said that it can be rollbacked and asked me to go through the details of the behind the scene operation of both delete and truncate and check it later.

Well i posting this question here not for just the definition and the things we know... but to get to the core of it. Throwing light on this aspect will be highly appricieated ...

Thanks in advance

Answer 1

Apparently the idea that truncate can't be rolled back is a myth.

Summary

• Truncate can be rolled back from within a transaction. The difference is that the truncate gets logged as a page deallocation in the log instead of a delete for each record being removed. Once the operation is committed it can't be undone.
• When you truncate, you are resetting the identity field. When you delete, you are not.
• You can't truncate a table that is referenced by a foreign key.
• Truncating will not fire any ON DELETE triggers

Answer 2

The truncate operation will deallocate all pages belonging to the table. These deallocations are logged and can be rolled back. Delete will mark all rows as deleted, and log each individual row.

Answer 3

Truncate can be rolled back if you do it right away in the transaction. It cannot be rolled back later from the transaction log as in a database recovery. Test it in a transaction and see.