Rolling back a truncate operation!

Posted Date: 24 Dec 2006	Resource Type: Articles	Category: Databases
Author: Vadivel Mohanakrishnan	Member Level: Diamond
Rating:	Points: 10

Introduction

First I suggest you to go through my earlier article on subject "Delete VS Truncate" here -- http://www.dotnetspider.com/kb/Article2866.aspx

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

Responses

No responses found. Be the first to respond and make money from revenue sharing program.

Feedbacks

Popular Tags

What are tags ?

Search Tags

Truncate . Rollback .

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.

	Next Resource: Delete VS Truncate ...
	Previous Resource: Fun with SQL Server ...
	Return to Discussion Resource Index
	Post New Resource
	Category: Databases

Post resources and earn money!

More Resources