In my last few blog posts, I’ve shared several methods of getting internal information from a database by using the DBCC PAGE command and utilizing the “WITH TABLERESULTS” option to be allowed to automate this process for further processing.

This post will also do this, but in this case, we’ll be using it to bust a common myth—data in a clustered index is physically stored on disk in the order of the clustered index.

To bust this myth, we’ll create a database, put a table with a clustered index into this database, and then we’ll add some rows in random order. Next, we will show that the rows are stored on the pages in logical order, and then we’ll take a deeper look at the page internals to see that the rows are not stored in physical order.

To start off with, let’s create a database and a table, and add a few rows to this table:

IF DB_ID('CIPageTest') IS NULL
CREATE DATABASE CIPageTest;
GO
-- use the database
USE CIPageTest;
GO
-- if the PageTest table exists, then drop it to start all over
IF OBJECT_ID('dbo.PageTest','U') IS NOT NULL DROP TABLE dbo.PageTest;
GO
-- create the dbo.PageTest table
CREATE TABLE dbo.PageTest (
RowID INTEGER PRIMARY KEY CLUSTERED,
Col1 VARCHAR(1000)
);
INSERT INTO dbo.PageTest (RowID, Col1) VALUES (1, REPLICATE('Row01', 100));
INSERT INTO dbo.PageTest (RowID, Col1) VALUES (3, REPLICATE('Row03', 100));
INSERT INTO dbo.PageTest (RowID, Col1) VALUES (5, REPLICATE('Row05', 100));
INSERT INTO dbo.PageTest (RowID, Col1) VALUES (2, REPLICATE('Row02', 100));
INSERT INTO dbo.PageTest (RowID, Col1) VALUES (4, REPLICATE('Row04', 100));

 

What we have is a table with 5 rows. The table’s clustered key is the RowID integer column. The rows are inserted so that the odd rows are inserted first, followed by the even rows.

At this point, let’s look at where the system reports these rows to be at. To do this, we’ll utilize two undocumented system commands.

You can read the rest of this article over here.