SQL Server Index Myth 5: Clustered Indexes Store Records in Physical Order

One of the more pervasive myths commonly held is the idea that a clustered index stores the records in a table in their physical order when on disk. This myth seems to be primarily driven by confusion between what is stored on a page and where records are stored on those pages. There is a difference between data pages and records. As a refresher, you’ll see a simple demonstration that dispels this myth.

Of the 8192 bytes on a page, approx. 8060 are available to you as a user. If you can manage to fit your data rows onto the page nicely, they’ll take up a lot less storage.

If your data row e.g. is 4100 bytes long, only a single row will be stored on a page (and the rest of the page – 3960 bytes – is wasted space). The important point is: those pages aren’t just relevant on disk, but also in SQL Server main memory –> you want to try to avoid large areas of space that cannot hold any useful information on a page.

If you can manage to reduce your row to take up 4000 bytes, then suddenly you can store two rows on a page, and thus significantly reduce the overhead of wasted space (down to 60 bytes per page).

To begin this example, execute the code in Listing 13. The code in the example will create a table named dbo.MythFive. Then it will add three records to the table. The last part of the script will output, using sys.dm_db_database_page_allocations, the page location for the table. In this example, the page with the records inserted into dbo.MythFive is on page 24189, shown in Figure 7. Image Note The dynamic management function sys.dm_db_database_page_allocations is a replacement for DBCC IND. This function, introduced in SQL Server 2012, provides an improved interface to examining page allocations for objects in a database over its DBCC predecessor.

Listing 3. Create and Populate MythFive Table

USE AdventureWorks2014
GO

IF OBJECT_ID('dbo.MythFive') IS NOT NULL
 DROP TABLE dbo.MythFive

CREATE TABLE dbo.MythFive
(
RowID int PRIMARY KEY CLUSTERED
,TestValue varchar(20) NOT NULL
);
GO

INSERT INTO dbo.MythFive (RowID, TestValue) VALUES (1, 'FirstRecordAdded');
INSERT INTO dbo.MythFive (RowID, TestValue) VALUES (3, 'SecondRecordAdded');
INSERT INTO dbo.MythFive (RowID, TestValue) VALUES (2, 'ThirdRecordAdded');
GO

SELECT database_id, object_id, index_id, extent_page_id, allocated_page_page_id,
 page_type_desc
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.MythFive'), 1, NULL, 'DETAILED')
GO

Figure 7. sys.dm_db_database_page_allocations output

The evidence to dispel this myth can be uncovered with the DBCC PAGE command. To do this, use the PagePID identified in Listing 8-13 with page_type_desc of DATA_PAGE. Since there is only a single data page for this table, that is where the data will be located.

For this example, Listing 14 shows the T-SQL required to look at the data in the table. This command outputs a lot of information that includes some header information that isn’t useful in this example. The portion that you need is at the end, with the memory dump of the page, as shown in Figure 8. In the memory dump, the records are shown in the order in which they are placed on the page. As the dump shows from reading the far-right column, the records are in the order in which they are added to the table, not the order that they will appear in the clustered index.

Listing 14. Create and Populate MythFive Table

DBCC TRACEON (3604);
GO

DBCC PAGE (AdventureWorks2014, 1, 24189, 2);
GO

Based on this evidence, it is easy to discern that clustered indexes do not store records in the physical order of the index. If this example were expanded, you would be able to see that the pages are in physical order, but the rows on the pages are not.

For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)

Alternatively click through the link   if you found this article interesting. (This will help the companies Search engine rankings)