Today I will again, because at the end of the day, a table is the most basic structure in a database and we need them to store the data, which is indeed the purpose of having a database, right?<\/p>\n
When explaining Heaps, Books online<\/a> specifies that <\/p>\n Most tables should have a carefully chosen clustered index unless a good reason exists for leaving the table as a heap.<\/p><\/blockquote>\n So if we see what a clustered index is, we also find in Books online<\/a> the following definition:<\/p>\n Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.<\/p><\/blockquote>\n I explained before that this order might be a bit different than you think<\/a>, but it’s pretty similar, and that bring some benefits, but also some limitations.<\/p>\n On one hand we have the order of the rows within a clustered index, which is enforced by the clustering key, and on the other we have that each page in SQL Server is exactly 8192 bytes, from those there are 8096 bytes usable and 96 bytes reserved for internal use, not much.<\/p>\n That shouldn’t be a problem when we have narrow tables that mostly use fixed length data types, but if we need to store very variable length columns your rows, your rows can vary from a few bytes to hundreds or thousands. Think about ‘Comments’ or ‘Long description’ we’ve all seen in the wild.<\/p>\n Let’s have a look to some queries <\/p>\n If now we do the same with a HEAP table, what’s going to happen?<\/p>\n So far, we’ve seen a similar behavior on both, clustered tables and heap, but we’ll soon see that they behave completely different for the future inserts we have going to make.<\/p>\n Let’s first have a look to the number of allocated pages <\/p>\n Now the fun starts, we can observe that while the Clustered index data pages have grown by 143 pages, the Heap has done it for just 2. Where is the trick then?<\/p>\n There is an undocumented function called sys.fn_PhysLocFormatter<\/a> which does what it says on the tin and returns the formatted physical location of any specific row within a table. (FileId:Page:SlotId)<\/p>\n Let’s have a look to the clustered index first:<\/p>\n
\nClustered index and page free space<\/strong><\/p>\n\r\nUSE master\r\nGO\r\nIF DB_ID('test') IS NOT NULL BEGIN\r\n\tALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\n\tDROP DATABASE test \r\nEND\r\nGO\r\nCREATE DATABASE test\r\nGO \r\n\r\nUSE test\r\nGO\r\n\r\nCREATE TABLE dbo.clustered_table(\r\nID INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED \r\n, Col1 NVARCHAR(4000) NOT NULL)\r\nGO \r\n\r\nINSERT INTO dbo.clustered_table (Col1) VALUES (REPLICATE (N'A', 3000))\r\nGO 1000\r\n\r\nDBCC IND('test', 'dbo.clustered_table', 1)\r\nGO\r\n\r\n<\/pre>\n![\"01_dbcc_ind_clustered_table\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/10\/01_DBCC_IND_clustered_table.png\")
<\/a><\/p>\n
\nIf we inspect one of the data pages we can see interesting information on its header.<\/p>\n\r\nDBCC TRACEON(3604)\r\nGO\r\n\r\nDBCC PAGE('test',1,144, 3)\r\nGO\r\n<\/pre>\n![\"02_dbcc_page_freespace\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/10\/02_DBCC_Page_freeSpace.png\")
<\/a><\/p>\n
\nSo we have that for each page the free space is 2079 bytes which makes more than 25% of the usable space (8096 bytes) not being used.<\/p>\n
\nHeap Table and page free space<\/strong><\/p>\n\r\nUSE test\r\nGO\r\n\r\nCREATE TABLE dbo.heap_table(\r\nID INT NOT NULL IDENTITY \r\n, Col1 NVARCHAR(4000) NOT NULL)\r\n\r\nGO \r\n\r\nINSERT INTO dbo.heap_table (Col1) VALUES (REPLICATE (N'A', 3000))\r\nGO 1000\r\n\r\nDBCC IND('test', 'dbo.heap_table', 1)\r\nGO\r\n\r\nDBCC TRACEON(3604)\r\n\r\nDBCC PAGE('test',1,179, 3)\r\n<\/pre>\n![\"03_dbcc_page_freespace_heap\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/10\/03_DBCC_Page_freeSpace_heap.png\")
<\/a><\/p>\n
\nWe can also see that the number of pages allocated for each table are pretty similar, <\/p>\n\r\nSELECT OBJECT_NAME(ix.object_id)\r\n\t\t, ix.name\r\n\t\t, au.*\r\n\t\t, p.rows\r\n\tFROM sys.indexes AS ix\r\n\t\tINNER JOIN sys.partitions AS p\r\n\t\t\tON p.object_id = ix.object_id \r\n\t\t\t\tAND p.index_id = ix.index_id\r\n\t\tINNER JOIN sys.allocation_units AS au\r\n\t\t\tON au.container_id = p.hobt_id\t\r\nWHERE ix.object_id IN (OBJECT_ID('dbo.heap_table'), OBJECT_ID('dbo.clustered_table'))\r\nGO\r\n<\/pre>\n![\"04_allocated_pages\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/10\/04_allocated_pages.png\")
<\/a><\/p>\n
\nNot very different, right? so where is the benefit? Why am I writing this then? \ud83d\ude42<\/p>\n
\nReusing empty space<\/strong><\/p>\n\r\nUSE test \r\nGO\r\n\r\nINSERT INTO dbo.heap_table\t\t(Col1) VALUES (REPLICATE (N'A', 500))\r\nINSERT INTO dbo.clustered_table (Col1) VALUES (REPLICATE (N'A', 500))\r\nGO 1000\r\n<\/pre>\n
\r\nSELECT OBJECT_NAME(ix.object_id)\r\n\t\t, ix.name\r\n\t\t, au.*\r\n\t\t, p.rows\r\n\tFROM sys.indexes AS ix\r\n\t\tINNER JOIN sys.partitions AS p\r\n\t\t\tON p.object_id = ix.object_id \r\n\t\t\t\tAND p.index_id = ix.index_id\r\n\t\tINNER JOIN sys.allocation_units AS au\r\n\t\t\tON au.container_id = p.hobt_id\t\r\nWHERE ix.object_id IN (OBJECT_ID('dbo.heap_table'), OBJECT_ID('dbo.clustered_table'))\r\nGO\r\n<\/pre>\n![\"05_allocated_pages_2000_rows\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/10\/05_allocated_pages_2000_rows.png\")
<\/a><\/p>\n\r\nSELECT *\r\n\t\t, sys.fn_PhysLocFormatter (%%physloc%%) AS row_location \r\n\tFROM dbo.clustered_table\r\n<\/pre>\n
![\"06_physlocformatter_clustered_index\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/10\/06_physlocformatter_clustered_index.png\")
<\/a><\/p>\n![\"07_physlocformatter_heap\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/10\/07_physlocformatter_heap.png\")
<\/a><\/p>\n