Myth: Truncate cannot be rolled back as it is not logged

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:
Tran_Delete

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:
Tran_Truncate

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.

Advertisements

Myth: Primary Key and Clustered index

Myth:
“When you create a PRIMARY KEY on table, Clustered index is creating by default”
Or
“Primary Key will create a clustered index by default”

Explanation:
I heard these statements many times and recenty, l heard from one of my collegues as well. So thought of sharing with you that the statement is a myth. Though, MSDN article says it very clearly, some of people are not learning or reading it with its completeness.

Please find the Excerpt from MSDN Article:

When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index..

Now, Let us look at something practical.

First, I would like to create a table with no clustered index(Heap) and define a primary key on the table.[Heap is a table without clustered index.]

Create Table PRIMARYKEY_MYTH(Column1 int NOT NULL,Column2 int)

Alter Table PRIMARYKEY_MYTH Add constraint PK__PRIMARYKEY_MYTH__Column1 PRIMARY KEY(Column1)

sp_help 'PRIMARYKEY_MYTH'

PK__PRIMARYKEY_MYTH__Column1	clustered, unique, primary key located on PRIMARY	

Drop table PRIMARYKEY_MYTH

From the above, we can clearly say that Primary Key is creating a Clustered index on a table.

Second, we need to create a Primary key on a clustered table(Already a clustered index is created on a table)

Create Table PRIMARYKEY_MYTH(Column1 int NOT NULL,Column2 int)

create clustered index IX__PRIMARYKEY_MYTH__Col on PRIMARYKEY_MYTH(Column2)

Alter Table PRIMARYKEY_MYTH Add constraint PK__PRIMARYKEY_MYTH__Column1 PRIMARY KEY(Column1)

sp_help 'PRIMARYKEY_MYTH'
--Results of the sp_help

IX__PRIMARYKEY_MYTH__Col	clustered located on PRIMARY		                
PK__PRIMARYKEY_MYTH__Column1	nonclustered, unique, primary key located on PRIMARY	

Drop table PRIMARYKEY_MYTH

Here, it is very clear, Primary Key on a clustered table is only creating a nonclustered unique index on the table, not the Clustered index.

Hence, I would request you to pronounce the complete statement as

When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique non clustered index..

I would like to request you to share your thoughts on the same.