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
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
Comments
"truncate table cannot be rolled back." here http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html
You may need to remove that there
http://sqlblogcasts.com/blogs/madhivanan