This is my very first blog and I got the inspiration to start only because of misunderstanding that exists on truncate command.
The question is can we rollback Truncate?
In many webistes and forums I found the answer to be a big NO and I know many who reading this blog have the same mind set.
The exact answer to the question is YES..Amazed??
we can verify the same by simple scripts
Just create a simple table insert some values truncate that and run rollback.
USE RKN
CREATE TABLE TEST(NAME CHAR('10'))
SELECT * FROM TEST
This will give an empty result
INSERT INTO TEST VALUES('RKNAIR')
GO 1000
Will insert 1000 records
Now I am truncating the table
BEGIN TRAN
TRUNCATE TABLE TEST
We can cross check whether the table got truncated by selecting the whole table.
SELECT * FROM TEST
This will give empty result as expected.
Now execute the rollback command
ROLLBACK
We can check the roll back worked by again issusing the select.
SELECT * FROM TEST
AMAZED????? :-)
Yes we can rollback Truncate.
Many believe truncate that truncate is a no logged operation which is far from truth. Truncate is MINIMALLY LOGGED and can be rolled back.
Hope this will help.
Please post your comments .
Queries written in a transaction are meant to rollback in-case we need it to rollback... I mean tats the reason why put it in a Transaction.. Anyways good piece of information mate...
ReplyDeleteKeep going :)
Thanks Ratheesh ..for this great info .... thanks lot .. it is really useful .... Bill Gates..
ReplyDeleteGood Catch Man!!The main difference between the two are,
ReplyDelete'Delete' logs(deletion of each row gets logged and physically deleted) the transaction and 'Truncate' does not log each row.Also Truncate does not do anything on the table row level.It logs the deallocation of the data pages in which the data exists. The deallocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. While doing a 'Truncate' this single operation will be logged. and while 'rollback' it will restore the link with datapage -
Thanks for the above tutorial.In your first blog you explained it so well.Really you did a good job.I am also found the answer of no in rollback of the truncate. But now I will try it must.
ReplyDeleteelectronic signatures