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

views:

171

answers:

+14 Q:

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

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

+15 A:

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

Joe Philllips 2010-08-26 14:45:01

Is any of this dependent on how the RDBMS is implemented? In looking at the Oracle documentation, it very specifically says that Truncate cannot be rolled back (http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_10006.htm)

Dante617 2010-08-26 15:02:15

The op tagged sql-server so this answer is specific to that. I do not know how Oracle works.

Joe Philllips 2010-08-26 15:04:30

You're right - I missed that.

Dante617 2010-08-26 15:05:48

while technically accurate this is entirely misleading. WHen someone says "Rolled Back", they do not mean only a part of the operation, they mean the entire operation, ALL the changes which occurred as part of the transactiomn, arguably, the most important of which are the data rows. Not the page allocations... Rolling back the page allocations hardly satisfies the commonly understood meaning of the term RollBack

Charles Bretana 2010-08-26 15:14:10

@Charles - But if you rollback the page deallocations you do rollback the entire operation.

Martin Smith 2010-08-26 15:16:33

@Joe Philiips: another point is that truncating does not fire any ON DELETE triggers on the table. For some people that's completely obvious (you aren't deleting) but for others it isn't (the data is gone so it was deleted).

Pondlife 2010-08-26 15:26:20

@Martin, Well, it is a good day when I learn something new... I would have never guess that the database locking mechanism applied to page allocations as well as actual data... This apparently works because until the Tx is committed, no other t4ransaction (or other process on the same machine), can use those Pages on the Disk. I wonder... are you sure about this? If the transaction was left open, and you had some other process reading and writing to the disk... or perhaps this magic is happening in SQL IO Cache memory ?

Charles Bretana 2010-08-26 15:27:36

Nice, I wrote that article a while back :-)

SQLMenace 2010-08-26 15:33:39

mmm... looking at the discussion i derieve that there are cases under which the truncate can be rolled back. Does anyone have any msdn reference to this topic ?

HotTester 2010-08-27 08:25:55

+4 A:

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.

HLGEM 2010-08-26 14:45:44

+4 A:

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.

Remus Rusanu 2010-08-26 14:59:34

+1 A:

So in the end you can rollback a Truncate as part of a transaction but not after being committed?

Cericme 2010-08-26 15:10:29

Yes, the same as any other DML operation. If you UPDATE a value and then COMMIT, there is no bult-in way to get the previous value back again.

Pondlife 2010-08-26 15:27:38

ansaurus

tags:

views:

answers:

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

Summary

related questions