Tags

, ,


The general perception is that TRUNCATE cannot be rolled back..right?? I remember when I was starting my career, I went for an interview in some MNC that time I was asked if TRUNCATE can be rolled back and I said “Yes” and the interviewer disagreed. And you guessed it right that I was not selected in that round ..

But today I am going to show you that we can indeed roll back the TRUNCATE command.  TRUNCATE command can be rolled back if it is used inside a TRANSACTION. As TRUNCATE is a DDL (Data Definition Language) statement so it cannot be rolled back if it is not inside a a Transaction or if the Transaction is COMMITTED.

Note: TRUNCATE is a logged operation, but in a different way. TRUNCATE logs the Deallocation of the data pages in which data exists which means that your data exists in the data pages, but the extents have been marked as empty for reuse. This is the only reason TRUNCATE is faster than DELETE.

The following example shows that the TRUNCATE can indeed be rolled back:

— Changing the Database to Tempdb

Use tempDB
GO

— Create Test Table and Insert the Data
CREATE TABLE [dbo].Truncate_RollBack_Test (ID INT)

— Inserting records into the table created above
INSERT INTO [dbo].Truncate_RollBack_Test (ID)
SELECT 10
UNION ALL
SELECT 20
UNION ALL
SELECT 30
GO

— Check the inserted data
SELECT * FROM [dbo].Truncate_RollBack_Test
GO 

— Beginning of the Transaction
BEGIN TRAN

— WE ARE TRUNCATING THE TABLE..YAY!!!!!!
TRUNCATE TABLE [dbo].Truncate_RollBack_Test
GO

— Checking the table after TRUNCATING it.
SELECT * FROM [dbo].Truncate_RollBack_Test
GO 

— Rollback Transaction
ROLLBACK TRAN
GO

— Check the data after Rollback

SELECT * FROM [dbo].Truncate_RollBack_Test

GO 

See that the data still exists after TRUNCATE

— Dropping the Table
DROP TABLE [dbo].Truncate_RollBack_Test
GO

I hope it will help to clear the doubts regarding the TRUNCATE operation in SQL Server.

 Cheers,
Subhro Saha

Advertisements