Category: DDL inside transaction

Myth: Truncate cannot rollback

On By sqlpathyIn DDL inside transaction, SQL Server, T-SQL, Transaction, TruncateLeave a comment

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 CATALOGE
  • RECONFIGURE
  • BACKUP/RESTORE
  • KILL
  • Some DBCC Commands
  • DDL statements related to memory-optimized tables (SQL 2014)
  • Natively compiled Stored Procedures

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

Advertisement