Bits.Bytes.

Being the kind of advanced chap that can walk and chew gum at the same time, I generally listen to music as I work. And a few minutes ago I was listening to Kanye West’s I Wonder, from his Graduation Album. Brilliant does not begin to describe it. You need to listen to that song.

But I digress.

Point is, as I typed away at my SQL Server 2005 Management studio and flipped between a random PDF that said that it was impossible to roll back truncates, I Wondered …

And if at first you don’t succeed, try try it yourself.

First, a dummy database to hold all our experiments

   1: create database Springfield

Next create a table to store the test data

   1: create table Residents(

   2:     ID int identity,

   3:     FirstName varchar(50),

   4:     Age int

   5: )

Next create some sample data. For the slower ones among us I’m sure a pattern is emerging!

   1: insert into Residents(FirstName,Age) values (‘Homer’, 40)

   2: insert into Residents(FirstName,Age) values (‘Marge’, 35)

   3: insert into Residents(FirstName,Age) values (‘Bart’, 11)

   4: insert into Residents(FirstName,Age) values (‘Lisa’,  

   5: insert into Residents(FirstName,Age) values (‘Maggie’, 3)

Now select and see our nice clean data

Awesome.

Now from past experience, truncate and delete affect identity columns differently. To make our experiment a bit less misleading, what we’ll do is to find a way to make the IDs NOT begin from 1. A simple delete will suffice, then we recreate our residents again

   1: delete from Residents

   2: go

   3: insert into Residents(FirstName,Age) values (‘Homer’, 40)

   4: insert into Residents(FirstName,Age) values (‘Marge’, 35)

   5: insert into Residents(FirstName,Age) values (‘Bart’, 11)

   6: insert into Residents(FirstName,Age) values (‘Lisa’, 8 )

   7: insert into Residents(FirstName,Age) values (‘Maggie’, 3)

Our data now looks like this

Right. Now let’s get cracking.

Even the most shiny faced new programmer is aware of the mysterious thing called the transaction that allows you to recover data from the live table you delete my mistake preserve atomicity of transactions. So what we’ll do is create  a transaction and within it delete all the data and roll it back

   1: begin tran

   2: delete from Residents;

   3: select * from Residents;

   4: rollback;

   5: select * from Residents;

Our data will now look like this. Which should be the same as above

Right. Now let’s try the same query with truncate instead of delete

   1: begin tran

   2: truncate table Residents;

   3: select * from Residents;

   4: rollback;

   5: select * from Residents;

The results pane should be as follows:

Observations

My dear Watson, I do believe the following

1. The truncate has been rolled back, just like the delete
2. The truncate appears to have preserved the identity values, and not started again from 1 (I’d be amazed if it did otherwise, this being a rollback and all, but one should not assume!).

Moral

Truncates can be rolled back, at least for SQL 2005. I’ll try and find a SQL 2000 box somewhere and see if it also holds true there.

Caveat

This is not always true. Truncates fail spectacularly if the table being truncated, hereby called the truncatee, is participating in a foreign key relationship. To be exact, if is being referenced.