DBA Myths: TRUNCATE TABLE deletes all the rows of a table instantly.
7April 30, 2014 by Kenneth Fisher
Most DBAs and developers I’ve talked to over the years have felt that TRUNCATE TABLE is an instant DELETE. However would you believe that it doesn’t actually delete the data from the pages at all? Here is an example:
-- Setup code
CREATE DATABASE TruncateTest
GO
USE TruncateTest
GO
CREATE TABLE dbo.TruncateTable (Col1 varchar(500),
Col2 varchar(500))
GO
INSERT INTO dbo.TruncateTable VALUES (REPLICATE('A',500),
'Here I Am')
GO 500
-- Get a page id. I'm going to pick out
-- one of the later data pages for no particular reason.
SELECT allocated_page_file_id, allocated_page_page_id,
page_type_desc, *
FROM sys.dm_db_database_page_allocations(db_id(),
OBJECT_ID('TruncateTable'), NULL, NULL, 'DETAILED')
-- Check space used by the table
EXEC sp_spaceused 'dbo.TruncateTable'
GO
-- Required so that the DBCC PAGE output will display
-- on the screen.
DBCC TRACEON (3604);
GO
-- Show the contents of the data page.
DBCC PAGE ('TruncateTest', 1, 325, 3);
GO
Here is the space currently taken up by the table.
And here is the first part of the contents of the page.
PAGE: (1:325) BUFFER: BUF @0x00000001FA0FA8C0 bpage = 0x00000001E8476000 bhash = 0x0000000000000000 bpageno = (1:325) bdbid = 11 breferences = 0 bcputicks = 0 bsampleCount = 0 bUse1 = 41794 bstat = 0xb blog = 0x1cc bnext = 0x0000000000000000 PAGE HEADER: Page @0x00000001E8476000 m_pageId = (1:325) m_headerVersion = 1 m_type = 1 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000 m_objId (AllocUnitId.idObj) = 84 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594043432960 Metadata: PartitionId = 72057594039042048 Metadata: IndexId = 0 Metadata: ObjectId = 245575913 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 4 m_slotCnt = 11 m_freeCnt = 2332 m_freeData = 5838 m_reservedCnt = 0 m_lsn = (33:440:2) m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 0 DB Frag ID = 1 Allocation Status GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x42 ALLOCATED 80_PCT_FULL DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED Slot 0 Offset 0x60 Length 522 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS Record Size = 522 Memory Dump @0x000000000AC5A060 0000000000000000: 30000400 02000002 0001020a 02414141 41414141 0............AAAAAAA 0000000000000014: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000028: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 000000000000003C: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000050: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000064: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000078: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 000000000000008C: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000000A0: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000000B4: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000000C8: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000000DC: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000000F0: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000104: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000118: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 000000000000012C: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000140: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000154: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000168: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 000000000000017C: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000190: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000001A4: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000001B8: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000001CC: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000001E0: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000001F4: 41414141 41414141 41414141 41486572 65204920 AAAAAAAAAAAAAHere I 0000000000000208: 416d Am Slot 0 Column 1 Offset 0xd Length 500 Length (physical) 500 Col1 = AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA Slot 0 Column 2 Offset 0x201 Length 9 Length (physical) 9 Col2 = Here I Am Slot 1 Offset 0x26a Length 522
I’m only showing the page header and the first row on the page since 8k of information is a bit much to put in a blog.
Next we truncate the table.
-- Truncate the table TRUNCATE TABLE dbo.TruncateTable GO
Now we run the same tests as before
-- Check the space used by the DB
after the truncate. There is no change
EXEC sp_spaceused
GO
-- Check space used by the table after the truncate.
EXEC sp_spaceused 'dbo.TruncateTable'
GO
-- Display the actual page.
DBCC PAGE ('TruncateTest', 1, 325, 3);
GO
Note the table now shows 0 rows and 0 space used.
If you skip down past the header you will see that the data portion has not changed.
PAGE: (1:325) BUFFER: BUF @0x00000001FA0FA8C0 bpage = 0x00000001E8476000 bhash = 0x0000000000000000 bpageno = (1:325) bdbid = 11 breferences = 0 bcputicks = 0 bsampleCount = 0 bUse1 = 42120 bstat = 0xb blog = 0x1cc bnext = 0x0000000000000000 PAGE HEADER: Page @0x00000001E8476000 m_pageId = (1:325) m_headerVersion = 1 m_type = 1 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000 m_objId (AllocUnitId.idObj) = 84 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594043432960 Metadata: PartitionId = 72057594039042048 Metadata: IndexId = 0 Metadata: ObjectId = 245575913 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 4 m_slotCnt = 11 m_freeCnt = 2332 m_freeData = 5838 m_reservedCnt = 0 m_lsn = (33:440:2) m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 0 DB Frag ID = 1 Allocation Status GAM (1:2) = NOT ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x2 80_PCT_FULL DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED Slot 0 Offset 0x60 Length 522 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS Record Size = 522 Memory Dump @0x000000000138A060 0000000000000000: 30000400 02000002 0001020a 02414141 41414141 0............AAAAAAA 0000000000000014: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000028: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 000000000000003C: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000050: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000064: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000078: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 000000000000008C: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000000A0: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000000B4: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000000C8: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000000DC: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000000F0: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000104: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000118: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 000000000000012C: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000140: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000154: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000168: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 000000000000017C: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 0000000000000190: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000001A4: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000001B8: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000001CC: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000001E0: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA 00000000000001F4: 41414141 41414141 41414141 41486572 65204920 AAAAAAAAAAAAAHere I 0000000000000208: 416d Am Slot 0 Column 1 Offset 0xd Length 500 Length (physical) 500 Col1 = AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA Slot 0 Column 2 Offset 0x201 Length 9 Length (physical) 9 Col2 = Here I Am Slot 1 Offset 0x26a Length 522
If you look for the GAM entry on line 32 you will see that it has gone from ALLOCATED to UNALLOCATED. This is because the page is no longer assigned as a data page for the table even though the data is still available. Heck even the column information is still there. This data will remain until the page is re-allocated for a new use.
Here is some additional reading you can do on how truncate works and the undocumented commands that I used.
Information about sys.dm_db_database_page_allocations
Additional information on how the truncate command works behind the scenes
More reading I just found out about. Also the way this works is covered under the term “deferred-drop”. Basically the work happens later behind the scenes.
SQL Studies 
What did you expect? Truncate is a quick operation. Do you want to perform some kind of secure deletion? That will take a lot longer. Computers allocate and deallocate memory, but the data is still there until it’s overwritten. I thought everybody knew that, but then again I’ve never been a dba and I’ve been retired from computing for a while.
Well levels of knowledge as well as areas of knowledge vary greatly in the tech field. There is a lot of knowledge out there and people sharing that knowledge in their own way is a wonderful thing. Some things that might seem as obvious and universally known to you. Could be something someone else never heard of or thought about before.
Something like truncate is a perfect example of something that some people probably just use and don’t really think about what it is doing on a more in depth level.
I have known some DBAs and programmers that have never l even looked at a execution plan. They just write a query or stored procedure and know that if give them back the data they want. The don’t think about the underlying process of what is going on to retrieve that data.
Nice write up on an important misconception.
Thanks!
Thanks for sharing such nice info in depth with examples.
It should be no suprise. Truncate is a fast low overhead operation. That’s a good thing
Agreed. It’s how it works that I found interesting.