Discussion Point:
Once again, I heard “Truncate cannot be rolled back where as DELETE can.”. There are many posts available in net regarding and still many people believe that truncate can not be rolled back within a transaction.
Lets us test and see the facts here.
Step 1: To understand better, We are going to create a test table and load some data to the test table as below:
--Create test table - Test_Rollback
create Table Test_Rollback(ProductID int, Product_Name Varchar(MAX))
Go
--Data generation to Test_Rollback table
;With cte as
(
Select 1 ProductID, Cast('a' as varchar(MAX)) Product_Name
Union All
Select ProductID +1 , Replicate(Cast('a' as varchar(MAX)),ProductID +1)
From Cte A where PRoductId <= 1000
)
Insert into Test_Rollback
Select ProductID, Product_Name From cte Option(MAXRECURSION 1000)
--Check the Table data
Select * From Test_Rollback
Here, we inserted 1001 records to Test_Rollback table to proceed with our testing.
Step 2: Now, We are going to DELETE records from the table and see the effect of ROLLBACK. In addition, we are also going to capture the number of logs generated during the delete operation.
CheckPoint
--Delete operation
Begin Tran Tran_Delete
Delete From Test_Rollback
Select Description,[Transaction Name],* From fn_dblog(NULL,NULL) WHERE [AllocUnitNAme] = 'dbo.Test_Rollback' --1001 Records
Rollback Tran Tran_Delete
Here, we issued a CHECKPOINT to move the active portion of the log forward to have a clarity for our testing. Please note that, CHECKPOINT does not mean that those logs are cleared or dumped, its still available in the log.
Once, the delete operation is completed, we used fn_dblog function (one among undocumented functions used to read the transaction log records from the active portion of transaction log file. Though fn_dblog is a very powerful function, as long as it is a undocumented function, please refrain the use of this function in production environment.) to read the transaction records. As fn_dblog function usually returns lots of information, we tried to filter information related to the AllocUnitName to our table (dbo.Test_Rollback). As we queried the information, we could find that there are 1001 records which indicates that the delete operation does at row level, each log for each record as below.
Snapshot:
Step 3: Now, We are going to TRUNCATE the table and see the effect of ROLLBACK. In addition, we are also going to capture the number of logs generated here as well.
Checkpoint
--Truncate Operation
Begin Tran Tran_Truncate
Truncate Table Test_Rollback
Select Description,[Transaction Name],* From fn_dblog(NULL,NULL) WHERE [AllocUnitNAme] = 'dbo.Test_Rollback' --40 Records
Rollback Tran Tran_Truncate
Here, we see the log records are only 40 which is far less than what we have seen with DELETE operation(1001). Closely looking at the Description, we can see that the description states that Deallocation of Extents are carried out during the TRUNCATE.See the below sample(only one sample is provided for understanding).
Deallocated 0001:00003528;Deallocated 0001:00003529;Deallocated 0001:0000352a;Deallocated 0001:0000352b;Deallocated 0001:0000352c;Deallocated 0001:0000352d;Deallocated 0001:0000352e;Deallocated 0001:0000352f
Deallocated 1 extent(s) starting at page 0001:00003528
Snapshot:
This is a clear indication that when a table is getting TRUNCATE, the TRUNCATE is deallocating at EXTENT level not at ROW level. Hence we could see the number of logs generated are considerably lesser than the DELETE operation.
Step 5: Now lets query the table and see for data existence.
--Check the Table data
Select * From Test_Rollback
Cool, we can see the data in the table. What does it mean? TRUNCATE can also be Rolled back within the transaction.
Step 5: As a final step, we are going to clean up our test table.
Select * from Test_Rollback
Drop table Test_Rollback
Conclusions: Let us conclude our findings here:
1. TRUNCATE is a logged operation, in fact does more efficiently by deallocating the extents.
2. TRUNCATE does a deallocation at Extent level not at ROW level.
3. As TRUNCATE does at Extent level, it logs lesser records in log file than DELETE. Hence, TRUNCATE would be faster than DELETE.
4. Finally, Debunk the Myth, TRUNCATE Table in a transaction can also be rolled back.
Hope this post would be useful, please share your thoughts.
SQLZealots 