{"id":226,"date":"2016-05-25T08:22:09","date_gmt":"2016-05-25T07:22:09","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=226"},"modified":"2016-05-25T08:28:46","modified_gmt":"2016-05-25T07:28:46","slug":"truncate-table-misconceptions","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2016\/05\/25\/truncate-table-misconceptions\/","title":{"rendered":"TRUNCATE TABLE misconceptions"},"content":{"rendered":"

There is a number misconceptions around TRUNCATE TABLE which have been around for too long that people take them as the real truth, let’s debunk some of those 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. <\/p>\n

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

 
\nTRUNCATE TABLE cannot be ROLLBACK<\/em><\/strong><\/p>\n

I chose this one first because it’s just so easy to prove that I’m surprised people still repeat it.<\/p>\n

Let’s go see how that is not true<\/p>\n

\r\nUSE AdventureWorks2014\r\nGO\r\nBEGIN TRAN\r\n\r\nTRUNCATE TABLE [dbo].[AWBuildVersion]\r\nSELECT * FROM [dbo].[AWBuildVersion]\r\n\r\nROLLBACK\r\nGO\r\nSELECT * FROM [dbo].[AWBuildVersion]\r\n<\/pre>\n
\"01_Truncate_rollback\"<\/a><\/p>\n
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.<\/p>\n
 
\nTRUNCATE TABLE deletes all the rows<\/em><\/strong><\/p>\n
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<\/a>, we can find TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data<\/em>. <\/p>\n
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.<\/p>\n
To see how that happens, let’s get first all pages allocated to our table<\/p>\n
\r\nUSE AdventureWorks2014\r\nGO\r\nDBCC IND('AdventureWorks2014','dbo.AWBuildVersion', 0)\r\n<\/pre>\n
\"02_DBCC_IND\"<\/a><\/p>\n
We have to 2 pages, one IAM page (PageType 10) and one DATA page (PageType 1), see more reference here<\/a><\/p>\n
Let’s check what’s inside those pages<\/p>\n
\r\nDBCC TRACEON (3604)\r\n\r\nDBCC PAGE('AdventureWorks2014', 1, 883, 3)\r\n\r\n\/*\r\nIAM: Single Page Allocations @0x0000000010D5A08E\r\n\r\nSlot 0 = (1:882)                    Slot 1 = (0:0)                      Slot 2 = (0:0)\r\nSlot 3 = (0:0)                      Slot 4 = (0:0)                      Slot 5 = (0:0)\r\nSlot 6 = (0:0)                      Slot 7 = (0:0)                      \r\n*\/\r\n<\/pre>\n
This is the IAM page and we can see that our data page (882) has an entry on it.<\/p>\n
You can see the other page just contain the single row existing in the table<\/p>\n
\r\nDBCC PAGE('AdventureWorks2014', 1, 882, 2)\r\n\/*\r\nDATA:\r\n\r\n\r\nMemory Dump @0x00000000125CA000\r\n\r\n00000000125CA000:   01010000 20020001 00000000 00001500 00000000  .... ...............\r\n00000000125CA014:   00000100 d6000000 701f8e00 72030000 01000000  ....\u00d6...p.\u008e.r.......\r\n00000000125CA028:   2a000000 78130000 16000000 5a050000 00000000  *...x.......Z.......\r\n00000000125CA03C:   56a4b06a 00000000 00000000 00000000 00000000  V\u00a4\u00b0j................\r\n00000000125CA050:   00000000 00000000 00000000 00000000 30001500  ................0...\r\n00000000125CA064:   01200f49 00d8a200 00000000 0062a300 00040000  . .I.\u00d8\u00a2......b\u00a3.....\r\n00000000125CA078:   01002e00 31003200 2e003000 2e003100 38003000  ....1.2...0...1.8.0.\r\n00000000125CA08C:   30000000 21212121 21212121 21212121 21212121  0...!!!!!!!!!!!!!!!!\r\n00000000125CA0A0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!\r\n00000000125CA0B4:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!\r\n*\/\r\n<\/pre>\n
 
\nNow I’m going to show you the same 2 pages after doing a TRUNCATE<\/p>\n
\r\nUSE AdventureWorks2014\r\nGO\r\nBEGIN TRAN\r\n\r\nTRUNCATE TABLE [dbo].[AWBuildVersion]\r\n\r\nDBCC PAGE('AdventureWorks2014', 1, 883, 3)\r\n\r\n\/*\r\nIAM: Single Page Allocations @0x0000000012B5A08E\r\n\r\nSlot 0 = (0:0)                      Slot 1 = (0:0)                      Slot 2 = (0:0)\r\nSlot 3 = (0:0)                      Slot 4 = (0:0)                      Slot 5 = (0:0)\r\nSlot 6 = (0:0)                      Slot 7 = (0:0)                      \r\n*\/\r\n<\/pre>\n
And you can see how the entry where our data page was allocated before is no longer there.<\/p>\n
But, the data page is still there, completely untouched (only the header would reflect the change)<\/p>\n
\r\nDBCC PAGE('AdventureWorks2014', 1, 882, 3)\r\n\/*\r\nDATA:\r\n\r\nMemory Dump @0x00000000125CA000\r\n\r\n00000000125CA000:   01010000 20020001 00000000 00001500 00000000  .... ...............\r\n00000000125CA014:   00000100 d6000000 701f8e00 72030000 01000000  ....\u00d6...p.\u008e.r.......\r\n00000000125CA028:   2a000000 78130000 16000000 5a050000 00000000  *...x.......Z.......\r\n00000000125CA03C:   56a4b06a 00000000 00000000 00000000 00000000  V\u00a4\u00b0j................\r\n00000000125CA050:   00000000 00000000 00000000 00000000 30001500  ................0...\r\n00000000125CA064:   01200f49 00d8a200 00000000 0062a300 00040000  . .I.\u00d8\u00a2......b\u00a3.....\r\n00000000125CA078:   01002e00 31003200 2e003000 2e003100 38003000  ....1.2...0...1.8.0.\r\n00000000125CA08C:   30000000 21212121 21212121 21212121 21212121  0...!!!!!!!!!!!!!!!!\r\n00000000125CA0A0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!\r\n00000000125CA0B4:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!\r\n*\/\r\n\r\nROLLBACK\r\n<\/pre>\n
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.<\/p>\n
 
\nTRUNCATE TABLE It\u2019s not fully logged<\/em><\/strong><\/p>\n
This was already debunked by Paul Randal in a pluralsight Course called \u00abSQL Server: Myths and Misconceptions\u00bb<\/a> (which I strongly recommend), but I will add a demo to show you how this is just a misconception.<\/p>\n
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.<\/p>\n
First I’ll clear the log<\/p>\n
\r\nCHECKPOINT\r\n\r\nSELECT [Current LSN], Operation, [Transaction ID], AllocUnitName\r\n\t\t, [Page ID], [Lock Information], Description, [Transaction SID] \r\n\tFROM fn_dblog(NULL, NULL)\r\n<\/pre>\n
\"03_fn_fblog_before\"<\/a><\/p>\n
And now we can start our TRANSACTION <\/p>\n
\r\nBEGIN TRAN\r\n\r\nTRUNCATE TABLE dbo.[AWBuildVersion]\r\n\r\nSELECT [Current LSN], Operation, [Transaction ID], AllocUnitName\r\n\t\t, [Page ID], [Lock Information], Description, [Transaction SID] \r\n\tFROM fn_dblog(NULL, NULL)\r\n\r\nROLLBACK\r\n<\/pre>\n
\"04_fn_fblog_after\"<\/a><\/p>\n
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<\/em><\/strong>, 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!<\/p>\n
 
\nTRUNCATE TABLE drops and creates a new table<\/em><\/strong><\/p>\n
Well, since I have shown you what it does, I guess there is no need to expend much time on this. <\/p>\n
No, TRUNCATE table does not DROP and CREATE a new table, period.<\/p>\n
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. <\/p>\n
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,
\nIt’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.<\/p>\n
And that’s again the reason why TRUNCATE TABLE does not fire triggers, because it does not work with rows, works with pages.<\/p>\n
 
\nConclusion<\/strong><\/p>\n
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 \ud83d\ude42 ).<\/p>\n
Thanks for reading!<\/p>\n