17

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

HotTester
  • 5,620
  • 15
  • 63
  • 97
  • 4
    Sounds like a pointless interview gotcha question to me! – badbod99 Aug 26 '10 at 15:05
  • 3
    Not pointless at all: TRUNCATE can be extremely fast compared to DELETE, but you may not want or be able to do it because of other considerations (IDENTITY, constraints and triggers). Therefore, if you have to regularly empty a large table (e.g. data warehouse load) it is very valuable to know the differences. – Pondlife Aug 26 '10 at 15:29
  • Duplicate http://stackoverflow.com/questions/2763295/what-is-the-diffrence-between-truncate-and-delete-in-sql-server/2763332#2763332 – Shantanu Gupta Feb 01 '12 at 11:30

4 Answers4

18

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 Phillips
  • 49,743
  • 32
  • 103
  • 159
  • 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) – MikeTheReader Aug 26 '10 at 15:02
  • The op tagged sql-server so this answer is specific to that. I do not know how Oracle works. – Joe Phillips Aug 26 '10 at 15:04
  • 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 Aug 26 '10 at 15:14
  • 1
    @Charles - But if you rollback the page deallocations you do rollback the entire operation. – Martin Smith Aug 26 '10 at 15:16
  • @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 Aug 26 '10 at 15:26
  • @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 Aug 26 '10 at 15:27
  • 3
    Nice, I wrote that article a while back :-) – SQLMenace Aug 26 '10 at 15:33
  • 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 Aug 27 '10 at 08:25
  • "You can't truncate a table that is referenced by a foreign key" - I learned this the hard way. While it is much faster, TRUNCATE fails where DELETE works if the table is being referenced by foreign key. – Clark Vera Jan 15 '19 at 22:06
  • @ClarkVera Are you referring to cascade deletes? Otherwise the FK would have the same limitations at a truncate wouldn't it? – Joe Phillips Jan 15 '19 at 22:07
  • yes Joe , it would have the same limitations. However, even if the FK table has NO RECORDS (which was the case in my situation), it still returns an errors. It seems odd but it makes sense. – Clark Vera Jan 21 '19 at 15:20
5

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
  • 288,378
  • 40
  • 442
  • 569
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.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
1

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

Cericme
  • 146
  • 7
  • 2
    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 Aug 26 '10 at 15:27