Truncate can be Rollback in same session

When you are surrounding TRUNCATE in transaction block you can rollback Truncated table in the current session.

If you close the session where Truncate is surrounded by transaction block, you cannot rollback the table unlike DELETE.

Here we have a table with only one record

select * from temp2

id          c_address  zip

----------- ---------- -----------

1           delhi      326595

(1 row(s) affected)

Below we are opening a transaction block and truncating the table later we are doing rollback, to check the intermediate result we have put select statement in between.

Lets execute the transaction block and see the result of rollback on truncate.                                                                                                 

begin transaction   

       select * from temp2

       truncate table temp2

       select * from temp2

rollback tran

      

id          c_address  zip

----------- ---------- -----------

1           delhi      326595

(1 row(s) affected)

id          c_address  zip

----------- ---------- -----------

(0 row(s) affected)

So here we saw before truncate we had one record and after truncate we had no records.

Now the next statement which we fired is rollback, here we need to check whether truncate statement gets rollback or not.

Lets fire the select query to see what affect it made on truncate after rollback

select * from temp2

id          c_address  zip

----------- ---------- -----------

1           delhi      326595

Ah! Truncate has no affect when it is followed by rollback in transaction block like Delete Command.

Conclusion: Truncated record can be rollback in the same session.

Few minute spent with you my SQL Server, rejuvenate for hours  :)

Post Reference: Vikram Aristocratic Elfin Share