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.
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.
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).
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.
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.
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.