May
25
2016

TRUNCATE TABLE misconceptions

TRUNCATE TABLE in SQL Server is well known for what it does as feature, but how it actually works is easily misunderstood, and for long time, the same wrong information goes on and on.

Today’s post wants to show you some of those misconceptions and why they are untrue using demos anyone can go reproduce.

 
TRUNCATE TABLE cannot be ROLLBACK

I chose this one first because it’s just so easy to prove that I’m surprised people still repeat it.

Let’s go see how that is not true

USE AdventureWorks2014
GO
BEGIN TRAN

TRUNCATE TABLE [dbo].[AWBuildVersion]
SELECT * FROM [dbo].[AWBuildVersion]

ROLLBACK
GO
SELECT * FROM [dbo].[AWBuildVersion]

01_Truncate_rollback

That was easy, you can observe the first SELECT does not return any rows as it’s within the transaction scope, but once we issue a ROLLBACK, our row(s) will come back. I chose this table with a single row, but obviously rows will get back regardless the number.

 
TRUNCATE TABLE deletes all the rows

This is actually true, what is sometimes misunderstood is how TRUNCATE TABLE does the same job as DELETE without WHERE clause. Just a look to BOL, we can find TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data.

By deallocating pages instead of deleting each individual row, TRUNCATE runs immediately regardless the size of the table not like DELETE, where the more rows in the table, the longer will take to complete.

To see how that happens, let’s get first all pages allocated to our table

USE AdventureWorks2014
GO
DBCC IND('AdventureWorks2014','dbo.AWBuildVersion', 0)

02_DBCC_IND

We have to 2 pages, one IAM page (PageType 10) and one DATA page (PageType 1), see more reference here

Let’s check what’s inside those pages

DBCC TRACEON (3604)

DBCC PAGE('AdventureWorks2014', 1, 883, 3)

/*
IAM: Single Page Allocations @0x0000000010D5A08E

Slot 0 = (1:882)                    Slot 1 = (0:0)                      Slot 2 = (0:0)
Slot 3 = (0:0)                      Slot 4 = (0:0)                      Slot 5 = (0:0)
Slot 6 = (0:0)                      Slot 7 = (0:0)                      
*/

This is the IAM page and we can see that our data page (882) has an entry on it.

You can see the other page just contain the single row existing in the table

DBCC PAGE('AdventureWorks2014', 1, 882, 2)
/*
DATA:


Memory Dump @0x00000000125CA000

00000000125CA000:   01010000 20020001 00000000 00001500 00000000  .... ...............
00000000125CA014:   00000100 d6000000 701f8e00 72030000 01000000  ....Ö...p.Ž.r.......
00000000125CA028:   2a000000 78130000 16000000 5a050000 00000000  *...x.......Z.......
00000000125CA03C:   56a4b06a 00000000 00000000 00000000 00000000  V¤°j................
00000000125CA050:   00000000 00000000 00000000 00000000 30001500  ................0...
00000000125CA064:   01200f49 00d8a200 00000000 0062a300 00040000  . .I.Ø¢......b£.....
00000000125CA078:   01002e00 31003200 2e003000 2e003100 38003000  ....1.2...0...1.8.0.
00000000125CA08C:   30000000 21212121 21212121 21212121 21212121  0...!!!!!!!!!!!!!!!!
00000000125CA0A0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
00000000125CA0B4:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
*/

 
Now I’m going to show you the same 2 pages after doing a TRUNCATE

USE AdventureWorks2014
GO
BEGIN TRAN

TRUNCATE TABLE [dbo].[AWBuildVersion]

DBCC PAGE('AdventureWorks2014', 1, 883, 3)

/*
IAM: Single Page Allocations @0x0000000012B5A08E

Slot 0 = (0:0)                      Slot 1 = (0:0)                      Slot 2 = (0:0)
Slot 3 = (0:0)                      Slot 4 = (0:0)                      Slot 5 = (0:0)
Slot 6 = (0:0)                      Slot 7 = (0:0)                      
*/

And you can see how the entry where our data page was allocated before is no longer there.

But, the data page is still there, completely untouched (only the header would reflect the change)

DBCC PAGE('AdventureWorks2014', 1, 882, 3)
/*
DATA:

Memory Dump @0x00000000125CA000

00000000125CA000:   01010000 20020001 00000000 00001500 00000000  .... ...............
00000000125CA014:   00000100 d6000000 701f8e00 72030000 01000000  ....Ö...p.Ž.r.......
00000000125CA028:   2a000000 78130000 16000000 5a050000 00000000  *...x.......Z.......
00000000125CA03C:   56a4b06a 00000000 00000000 00000000 00000000  V¤°j................
00000000125CA050:   00000000 00000000 00000000 00000000 30001500  ................0...
00000000125CA064:   01200f49 00d8a200 00000000 0062a300 00040000  . .I.Ø¢......b£.....
00000000125CA078:   01002e00 31003200 2e003000 2e003100 38003000  ....1.2...0...1.8.0.
00000000125CA08C:   30000000 21212121 21212121 21212121 21212121  0...!!!!!!!!!!!!!!!!
00000000125CA0A0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
00000000125CA0B4:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
*/

ROLLBACK

That is how we can ROLLBACK the statement, the SQL engine only has to allocate the page(s) back in place and all would be again like nothing happened.

 
TRUNCATE TABLE It’s not fully logged

This was already debunked by Paul Randal in a pluralsight Course called “SQL Server: Myths and Misconceptions” (which I strongly recommend), but I will add a demo to show you how this is just a misconception.

Also from the two previous examples you might have realized that this is obviously not true, because how can you ROLLBACK something if there is no enough logging information? But let’s see that info to be certain.

First I’ll clear the log

CHECKPOINT

SELECT [Current LSN], Operation, [Transaction ID], AllocUnitName
		, [Page ID], [Lock Information], Description, [Transaction SID] 
	FROM fn_dblog(NULL, NULL)

03_fn_fblog_before

And now we can start our TRANSACTION

BEGIN TRAN

TRUNCATE TABLE dbo.[AWBuildVersion]

SELECT [Current LSN], Operation, [Transaction ID], AllocUnitName
		, [Page ID], [Lock Information], Description, [Transaction SID] 
	FROM fn_dblog(NULL, NULL)

ROLLBACK

04_fn_fblog_after

At first glance we can see that there are 20 rows where it was 3 rows before, so there is log activity, and if we look at the last two columns we can see how pages are deallocated. There is also some metadata updated along the way, but that proves TRUNCATE TABLE is fully logged, and paraphrasing Paul Randal it’s very efficiently logged, just imaging the amount of log records generated by a DELETE statement in a big table and compare it to just deallocating a few pages. Very smart and very efficient. Love it!

 
TRUNCATE TABLE drops and creates a new table

Well, since I have shown you what it does, I guess there is no need to expend much time on this.

No, TRUNCATE table does not DROP and CREATE a new table, period.

Some people might think so because of the impossibility of using TRUNCATE on a table which is referenced by a FOREIGN KEY constraint, but there is another explanation.

We have seen TRUNCATE does not look at the content of individual data pages, just simply deallocates them, so the engine CANNOT know if there is any row on those pages which is referenced by the other table,
It’d just be impossible to allow such operation without taking the risk of throwing away the referential integrity of our data, therefore is not permitted.

And that’s again the reason why TRUNCATE TABLE does not fire triggers, because it does not work with rows, works with pages.

 
Conclusion

Just to wrap up I want to give you some links you might find useful and recommend you don’t believe everything you read on the internet, specially if it does not come from trusted sources. (Or there is a demo you can reproduce 🙂 ).

Thanks for reading!

 

13 comments
  1. Arun Sirpal says:

    Mr Double G – I thought TRUNCATE uses the deferred drop queue? Or have I missed something here?
    p.s. LOVE your article 🙂

  2. Marty Galvan says:

    Double G,
    This was probably one of the most easily understandable explanations of truncate vs delete.
    This was/is great info to know. I appreciate your work in explaining it. Now I can sound smart trying to explain what the it does.

    MJG

  3. Henrik Nordling says:

    Interesting reading! Very well put together.

  4. Raul says:

    thanks @Marty and Henrik, I’m very happy you like it. But I’d be happier if this moves you to be more curious about internals which I find fascinating.
    SQL Server gives us the tools, and within the community you can get the knowledge to use those tools and experiment. You might be surprised how much fun it is on doing it!

    Cheers!

  5. perry whittle says:

    before the rollback is completed, a select in a separate session with an appropriate isolation level set would also return zero rows. That would have been a better way to debunk the myth, still, you prove the point with the rollback.

    • Ken says:

      In order to delay the rollback, put in a sleep command so you have time to transfer to the other session, select rows twice, once before and once after rollback. (manually run twice or put a sleep in between two select sessions too.)

    • Raul says:

      Thanks for your comments!, TRUNCATE will hold Exclusive Locks on Pages/Extents so other sessions will have to wait until those are released, because Exclusive locks are not compatible with any other lock.

      Even using (NOLOCK) or READ UNCOMMITTED you have to wait.

  6. Ken says:

    Whoops said “select sessions” meant “select statements”.

  7. ASK says:

    I believe two important differences are that TRUNCATE will clear index space too whereas DELETE increases the size of the index and TRUNCATE resets the value of identity columns if any to 1 whereas DELETE will not change the identity value

  8. mosaic says:

    You need dbo permission to truncate. You need only r/w to delete

Leave a Reply

Your email address will not be published. Required fields are marked *