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

Denis Gobo — Wed, 13 Jun 2007 15:07:00 GMT

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))

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))

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))

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

Search results matching tags 'SQL Server 2005', 'DELETE', and 'TRUNCATE'

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