This is a very common concept that truncate cannot be rolled back. Let us learn in today’s blog post that Rollback TRUNCATE is possible. I always hear the conversation between the developer if truncate can be rolled back or not. Let me just say it clearly today – TRUNCATE can be rolled back.
Truncate is indeed a logged operation, it just does not log removing the records but it logs the page deallocations. Whereas DELETE command actually logs every single record it removes from the table, hence it takes a bit longer to execute.
In any case, both of the keywords are logged and can be rolled back.
Following example demonstrates how during the transaction truncate can be rolled back.
The code to simulate the above result is here.
-- Create Test Table CREATE TABLE TruncateTest (ID INT) INSERT INTO TruncateTest (ID) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 GO -- Check the data before truncate SELECT * FROM TruncateTest GO -- Begin Transaction BEGIN TRAN -- Truncate Table TRUNCATE TABLE TruncateTest GO -- Check the data after truncate SELECT * FROM TruncateTest GO -- Rollback Transaction ROLLBACK TRAN GO -- Check the data after Rollback SELECT * FROM TruncateTest GO -- Clean up DROP TABLE TruncateTest GO
Well, I hope you find this example fun and also removes any confusion you may have on this topic. Here are a few related blog posts you should read on the same topic.
I hope from today’s blog post it is clear that rollback truncate command is possible. You can watch my videos on my youtube channel over here.
Reference: Pinal Dave (https://blog.sqlauthority.com)