Can I Rollback a Truncate?

By Greg No comments

There’s a common belief that you cannot rollback a truncate statement in SQL Server, but is that true? We can find out with a pretty simple test.

First, we need a table we can truncate. Obviously you don’t want to go and try this out on your Production tables, so lets create a test one:

CREATE TABLE dbo.YouCantTruncateMe
(
    ID INT PRIMARY KEY IDENTITY(1, 1),
    COL1 VARCHAR(1) NOT NULL
)

INSERT INTO YouCantTruncateMe
(
    COL1
)
VALUES
('a')

GO 1000

Pretty simple, 1000 rows of the letter a. So what happens when we truncate it without a Transaction?

TRUNCATE TABLE YouCantTruncateMe

As you expect, no rows left. Let’s insert them all back again and then see how it goes inside a transaction:

BEGIN TRAN
TRUNCATE TABLE YouCantTruncateMe

SELECT * FROM YouCantTruncateMe

ROLLBACK

When selecting the data inside the transaction it returns 0 rows again, but if you run it after the rollback, you can see that all 1,000 rows are back again.

The other interesting thing to note is that even when you have RCSI turned on, or if you use nolock, you can’t query the table while it’s being truncated. Truncate is a blocking transaction regardless of isolation level.

Leave a Reply