Rolling back TRUNCATE statements in SQL Server

There are two main statements used for deleting data from a table in SQL SERVER:

• TRUNCATE
• DELETE

The two commands achieve the same result; however, each of the commands acts a little bit different. Each of the two commands have advantages, limitations, and consequences, which have to be considered when deciding which method to use.

In this post we will concentrate in one aspect of these commands usage – the ability to rollback each operation.

DELETE statements delete rows one at a time, logging each row in the transaction log and maintaining the log sequence number (LSN) information.
Even though it will consumes more database resources, it comes in handy as these transactions can be rolled back if necessary by using the log files, when the recovery model of the database is set to full.

But what happens when you issue the TRUNCATE command?

The TRUNCATE statement is much faster statement than the DELETE command.
It deletes all records in a table by deallocating the data pages used to store the table's data. This operation has a limited logging in the transaction log (only the page deallocations are recorded in the transaction log). In addition fewer locks are acquired with this statement in compare to the DELETE statement.
As a result of the above it is commonly believed that records removed by the TRUNCATE statement cannot be restored – rolled back.

It is true in most cases, however DELETE and TRUNCATE can both be rolled back when they are executed inside a 'BEGIN TRANSACTION' block and the current session has not yet ended.
In other words, when the TRUNCATE statement has not been committed yet, it can be rolled back

The following code demonstrates a scenario when a TRUNCATE can be rolled back for that particular session.

view source print?

01	--Creating a testTable for the demonstration

02	CREATE TABLE TESTTABLE (ID INT)

03

04	--Inserting rows into the testTable.

05	INSERT INTO TESTTABLE

06	SELECT TOP 1000 OBJECT_ID FROM SYS.OBJECTS

07	BEGIN TRAN

08	--Truncating the table

09	TRUNCATE TABLE TESTTABLE

10	--No rows will return

11	SELECT * FROM TESTTABLE

12

ROLLBACK

13

14	--The original rows from testTable will return

15	SELECT * FROM TESTTABLE

16

17	--Dropping the testTable.

18	DROP TABLE TESTTABLE

To summarize,
DELETE can always be rolled back-

• When the recovery model of the database is set to SIMPLE, the statement can be rolled back – when the statement is executed inside a 'BEGIN TRANSACTION' block.
• When the recovery model of the database is set to FULL, the statement can also be recovered from the log files.

TRUNCATE can or cannot be rolled back, depends if it is executed from within a 'BEGIN TRANSACTION' block.