Myth: Truncate cannot rollback

There is plethora of reasons which people give when asked if truncate can rollback. Some say it does not rollback as it is a DDL (Data Definition Language) statement and DDL statements are not allowed in explicit transactions. Other view is that as truncate does not create entries in transaction log file it will not rollback. And many other reasons!

Truth is truncate can rollback!

Let us try to prove this fact; Code snippet below does the following:
1) Create table tblTruncate
2) Insert few values [I have inserted 3 values]
3) Truncate the table between an explicit transaction

CREATE TABLE tblTruncate
  (
     id INT
  )
GO

INSERT INTO tblTruncate(id) VALUES (1),(2),(3)
GO
-- Open transaction
BEGIN TRAN
-- Begining of the transaction
PRINT '*********Inside transaction***************'
PRINT ' '
SELECT 'No of records before truncate - ' + CONVERT(CHAR(2), COUNT(*))
FROM   tblTruncate
PRINT '*********Truncating table***************'
PRINT ' '
--Truncating table tblTruncate
TRUNCATE TABLE tblTruncate

SELECT 'No of records after truncate - ' + CONVERT(CHAR(2), COUNT(*))
FROM   tblTruncate

-- Transaction rollback
ROLLBACK TRAN
-- Transaction closed
PRINT '*********Outside transaction***************'
PRINT ' '

SELECT 'No of records after rollback - ' + CONVERT(CHAR(2), COUNT(*))
FROM   tblTruncate 

Here it the output log:

*********Inside transaction***************
 
No of records before truncate - 3 

*********Truncating table***************
 
No of records after truncate - 0 

*********Outside transaction***************

No of records after rollback - 3 

Clearly we can see that all the three records which were truncated from table tblTruncate were present after rollback.

Taking another example: Here we will create a table inside a transaction within rollback mode and try to check if table exists after rollback.

-- Pre-condition to check if table exists
IF OBJECT_ID('table_inside_transaction') IS NOT NULL
  PRINT 'Table exists!'
ELSE
  PRINT 'Table does not exist!' 
Print '***** Transaction begins *****'
print ''

--Open transaction
BEGIN TRAN

CREATE TABLE table_inside_transaction
  (
     id INT
  )

-- Checking if table is created inside transaction
IF OBJECT_ID('table_inside_transaction') IS NOT NULL
  PRINT 'Table exists!'
ELSE
  PRINT 'Table does not exist!' 
  
ROLLBACK TRAN 
-- Close transaction in rollback mode


Print '***** Transaction closed *****'
print ''
-- Checking if table exists
IF OBJECT_ID('table_inside_transaction') IS NOT NULL
  PRINT 'Table exists!'
ELSE
  PRINT 'Table does not exist!' 

Here is the output log generated:


Table does not exist!

***** Transaction begins *****
 
Table exists!

***** Transaction closed *****
 
Table does not exist!

Again, it is very clear that DDL transactions can rollback. Is it good to think at this stage that every DDL transaction can be executed within transaction?

Answer is NO!

All the DDL transactions can be executed inside transaction except the following:
CREATE/ALTER/DROP DATABASE
CREATE/ALTER/DROP FULLTEXT INDEX
CREATE/ALTER/DROP FULLTEXT CATALOG
RECONFIGURE
BACKUP/RESTORE

Another thing worth mentioning is that UPDATE STATISTICS can be used inside an explicit transaction. But it cannot be rolled back; the fact being UPDATE STATISTICS commits independently of the transaction in which it is enclosed.

Hope this post was helpful and set a group to bust myths around DDL inside transactions.

Happy Learning
Lokesh Vij

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s