THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

SQL Myth: Truncate Cannot Be Rolled Back Because It Is Not Logged

I am still amazed at how many people still think that TRUNCATE TABLE is not logged. There is some logging going on but it is minimal, here is what Books On Line says:

 

TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.

 

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.

 

Let’s prove that we can rollback a truncate

 

Create this table and do the select

 

CREATE TABLE dbo.Enfarkulator (ID int IDENTITY PRIMARY KEY, SomeOtherCol varchar(49))

GO

INSERT dbo.Enfarkulator VALUES(1)

INSERT dbo.Enfarkulator VALUES(1)

 

 

 

SELECT * FROM dbo.Enfarkulator

 

ID    SomeOtherCol

1     1

2     1

 

 

Now run this part

 

BEGIN TRAN

    TRUNCATE TABLE dbo.Enfarkulator

    SELECT * FROM dbo.Enfarkulator

ROLLBACK TRAN

 

 

ID    SomeOtherCol

(0 row(s) affected)

 

As you can see the table was truncated, now select from the table again

 

 

SELECT * FROM dbo.Enfarkulator

 

ID    SomeOtherCol

1     1

2     1

 

 

Yep, the data is there, proving that you can rollback a truncate and all the data will be there. There are two other major difference between truncate and delete which I will explain below.

 

Truncate doesn’t preserve the identity value but delete does

 

This is another difference between truncate and delete, truncate will reset the identity value but delete does not. Run the following code to see how that works

 

 

CREATE TABLE dbo.Enfarkulator2 (ID int IDENTITY, SomeOtherCol varchar(49))

GO

INSERT dbo.Enfarkulator2 VALUES(1)

INSERT dbo.Enfarkulator2 VALUES(1)

 

 

SELECT * FROM dbo.Enfarkulator2

SELECT * FROM dbo.Enfarkulator

 

 

DELETE dbo.Enfarkulator2

TRUNCATE TABLE dbo.Enfarkulator

 

INSERT dbo.Enfarkulator VALUES(1)

INSERT dbo.Enfarkulator2 VALUES(1)

 

SELECT * FROM dbo.Enfarkulator2

SELECT * FROM dbo.Enfarkulator

 

The Enfarkulator id was reset and the Enfarkulator2 id was not. In order to do the same with delete you will need to run a dbcc checkident reseed command. Here is the code for that.

 

DELETE dbo.Enfarkulator2

TRUNCATE TABLE dbo.Enfarkulator

 

DBCC CHECKIDENT (Enfarkulator2, RESEED, 0)

 

Now insert again and you will see that the values are the same.

 

INSERT dbo.Enfarkulator VALUES(1)

INSERT dbo.Enfarkulator2 VALUES(1)

 

SELECT * FROM dbo.Enfarkulator2

SELECT * FROM dbo.Enfarkulator

 

 

 

You can’t truncate tables that are referenced by a foreign key constraint.

 

If you have a table which is referenced by another table with a foreign key constraint then you cannot truncate that table. Here is the code for that

CREATE TABLE dbo.Enfarkulator3 (ID int IDENTITY, SomeOtherCol varchar(49))

GO

INSERT dbo.Enfarkulator3 VALUES(1)

 

 

Now let’s add the foreign key

 

ALTER TABLE dbo.Enfarkulator3 ADD CONSTRAINT [FK_Fark3_Fark]

FOREIGN KEY ([ID]) REFERENCES [dbo].[Enfarkulator] ([ID])

 

 

Now try to truncate.

 

TRUNCATE TABLE Enfarkulator

 

Server: Msg 4712, Level 16, State 1, Line 1

Cannot truncate table 'Enfarkulator' because it is being referenced by a FOREIGN KEY constraint.

 

See you cannot do that

 

--Clean up time  ;-)

DROP TABLE dbo.Enfarkulator3,dbo.Enfarkulator2,dbo.Enfarkulator

Published Wednesday, June 13, 2007 12:07 PM by Denis Gobo

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Denis Gobo said:

This is going to be a three part series. Part 1 The phone interview. Part 2 The face to face interview.

December 2, 2007 2:56 PM
 

Vadivel said:

Yep thats true we could rollback stuffs which are truncated -- http://vadivel.blogspot.com/2006/12/rolling-back-truncate-operation.html

December 5, 2007 1:46 PM
 

Narendra said:

Thanks for providing the information.I really did not know the correct answer between Truncate and delete

December 14, 2007 1:37 AM
 

Keshav said:

Thanks,

this doc cleared my doubt.

July 28, 2008 7:16 AM
 

Suvendu said:

Nice analysis. :-)

December 12, 2008 2:06 AM
 

Fazal said:

Very Nice Post.

Thank you very much!!

:)

January 25, 2010 2:00 AM
 

Ashish said:

These article cleared a real confusing concept of mine, thanks .

March 11, 2010 7:54 AM
 

denish said:

As you specified that truncate statement can’t be used when table is being referred as foreign key in another table but my question is that "SAME IS THE CASE WITH DELETE FROM". Even delete statement can't be used when table is being referred as foreign key constrain. Then how we can say that this is the difference between two statements?

March 14, 2010 1:29 AM
 

Pankajm said:

Nice work,

keeping going

June 29, 2010 9:25 AM
 

Pallavi said:

Great article!! thumbs up for this ..

but i have same doubt as denish that even DELETE FROM will not work if that table is referenced by any foreign key constraint.

August 3, 2010 12:44 AM
 

Babu said:

Simple and Neat. Impressed!

November 10, 2010 3:46 PM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement