I heard this one again last week, and I’m not sure why it’s still going around. Microsoft changed the behavior of Truncate Table to be able to be rolled back, but this myth had been taught to DBAs for so many years, that I think some haven’t tried it.
Very simply, Truncate Table can be rolled back since SQL Server 2005.
Let’s start by looking at the reasons for the inability to roll back a Truncate Table statement. In the late-Sybase/early-SQL Server days through SQL Server 2000, Truncate Table actually dropped the table and created a new one. This moved Truncate Table from pure DML, which is what we expect, to a combined DML/DDL statement, which could not be rolled back. This is seen by looking at the Object_ID for the table, and seeing that a new Object_ID is created during the Truncate Table statement.
Beginning with SQL Server 2005, Truncate Table does not drop and create, but rather unlinks the page list from the table. This unlinking is now logged, and can be rolled back using a standard transaction.
Let’s take a look at this in my trusty Sandbox database.
We’ll start by dropping the table if it exists, and creating a new one.
A quick Select will show the empty table.
Now, let’s add some data and view it.
Now, let’s truncate the table within a transaction and verify that the data is gone.
A Rollback will bring the data back.
So, as you can see, the Rollback restored the data.
The source code for this article is attached to this article.
