Rolling back a truncate operation!

First I suggest you to go through my earlier article on subject "Delete VS Truncate" here.

Truncate is also a logged operation, but in a different way. It logs the deallocation of the data pages in which the data exists.

Let us create a dummy table for understanding this example:

Create Table dbo.TruncateTblDemo (Sno int)
Go

Insert few records into it:

Insert into dbo.TruncateTblDemo values (1)
Insert into dbo.TruncateTblDemo values (2)
Insert into dbo.TruncateTblDemo values (3)
Go

You could see that the table has 3 records in it:

Select * from dbo.TruncateTblDemo

Execute a truncate statement:

Truncate Table dbo.TruncateTblDemo

After the above statement you can't retrieve the data back because it is an explicit transaction. Unless or until you have set Implicit_Transactions to off.

That is, Internally sql would have taken our above truncate statement as follow:

Begin Tran
Truncate Table dbo.TruncateTblDemo
Commit Tran

Select * from dbo.truncatetbldemo

Getting back data using Truncate!!

Let me insert the same records into that table for test purpose again.

Insert into dbo.TruncateTblDemo values (1)
Insert into dbo.TruncateTblDemo values (2)
Insert into dbo.TruncateTblDemo values (3)

Begin Tran
Truncate table dbo.truncatetbldemo
Rollback Tran

Now you could see those 3 records which were truncated:

Select * from dbo.truncatetbldemo

Clean up:

Drop table dbo.truncatetbldemo

Technorati tags: SQL Server, SQL Server 2005