Time for the ghouls and goblins to come out of the woodwork once again for another tale of deception and tyranny.  OK maybe not tyranny but definitely a haunting tale is to be told this month while trick-or-treaters plan their routes and mend their costumes.  We have SQueeLing experts suiting up for the adult party known as TSQL Tuesday.

This month the vampires, ghosts, and zombies are all breaking it down at Sankar Reddy’s place (Blog | Twitter).  The ghoulish stories to be shared are to be myths from the underSQLworld.

Truncating a Table is Unrecoverable

Have you heard this one in the past?  If you truncate a table, you cannot undo the action like you can with a delete.  What is the difference between the two methods to remove data from a table?

When you truncate a table, the pages are deallocated with minimal locking of the resources.  When you use the delete statement, the data is deleted row-by-row with more locks.  Everybody knows that a delete can be rolled back.  Many people believe that a Truncate cannot be rolled back.

Well, at this point we really need a table to test.  So let’s create a table and perform a few simple tests.

Code block

SELECT TOP 1000000
RollID       = IDENTITY(INT,1,1)
INTO dbo.TruncTabRollback
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2 
 
ALTER TABLE dbo.TruncTabRollback
ADD PRIMARY KEY CLUSTERED (RollID)

With the test table ready to go, we will begin with the baseline demonstration on the Delete command.  Here is the script to demonstrate that a Delete can be rolled back (but we already knew that).

Code block

BEGIN TRAN delrollback
 
DELETE TruncTabRollback
 
SELECT COUNT(*) AS PreRollBack
FROM TruncTabRollback
 
ROLLBACK TRAN delrollback
 
SELECT COUNT(*) AS PostRollBack
FROM TruncTabRollback

Pretty simple and straight forward – baseline is proven with that script.  The premise that a Delete can be rolled back has been established.  Now, how about that truncate statement?  For the truncate statement, I will use the same simple script, substituting the appropriate commands in this script.

Code block

BEGIN TRAN delrollback
 
TRUNCATE TABLE TruncTabRollback
 
SELECT COUNT(*) AS PreRollBack
FROM TruncTabRollback
 
ROLLBACK TRAN delrollback
 
SELECT COUNT(*) AS PostRollBack
FROM TruncTabRollback

When this script is executed, we see that indeed the data is recoverable.  Here are the results I get from the truncate and associated rollback script.

Conclusion

I think this is one of those myths that is frequently floated throughout the SQL world due to another myth that is related to the Truncate statement.  It is often explained that you can’t recover a truncate because the truncate is not logged.  That is also obviously wrong (if it weren’t logged, how could you recover the table without a restore?).  The myth about Truncate being non-logged has already been debunked (as has this one – in a different series) by Paul Randal in his Myth-A-Day series.  The key to this is that the Truncate is logged and that you wrap the command in a transaction – and only commit when you are ready.