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]
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)
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)
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
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!
Mr Double G – I thought TRUNCATE uses the deferred drop queue? Or have I missed something here?
p.s. LOVE your article 🙂
Thanks! No, you haven’t missed anything, the deferred drop will still happen if required, but at the PAGE level to avoid memory issues while deallocating a big amount of pages. (not as in my example where there are 2 pages)
TRUNCATE TABLE will complete immediately whatsoever but might not log everything immediately because might be using the deferred queue.
You can find more information about it in http://sqlperformance.com/2013/05/sql-performance/drop-truncate-log-myth where Paul explains when this change was applied and more details.
Cheers!
Cool – rings a bell from IE1.
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
Interesting reading! Very well put together.
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!
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.
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.)
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.
Whoops said “select sessions” meant “select statements”.
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
You need dbo permission to truncate. You need only r/w to delete
Thanks for your comment!, the minimum permission required is ALTER TABLE, no need to be db_owner. Please refer to https://msdn.microsoft.com/en-gb/library/ms177570.aspx#Anchor_5
Cheers