An Introduction to Clustered and Non-Clustered Index Data Structures

The output will display the data however it was entered in the table. This is how SQL stores the data in pages. Actually, 26255 & 26409 will display the data page.

I have displayed the data page information for page 26255 only. This is how MS-SQL stores the contents in data pages as such column name with its respective value.  

Record Type = PRIMARY_RECORD                       

EmpId          = 4

EmpName    = dddddddddddddddddddddddddddddddddddddddddddddddd
dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

Record Type = PRIMARY_RECORD                       

EmpId           = 6

EmpName     = ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff

Record Type = PRIMARY_RECORD                       

EmpId           = 1

EmpName     = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

This displays the exact data storage in SQL without any index on table. Now, let’s go and create a Unique Clustered Index on EmpID column.

Create a Clustered Index

Now, let us create a unique clustered index on empid column to see how it affects the data that is stored in SQL Server.

CREATE UNIQUE CLUSTERED INDEX DummyTable2_EmpIndex
ON DummyTable2 (EmpID)
GO

Execute:

Select EmpID From DummyTable2

Here are the results:

Now, execute the DBCC ind (dbid, tabid, -1)

DBCC TRACEON (3604)
GO

Declare @DBID Int, @TableID Int

Select @DBID = db_id(), @TableID = object_id(‘DummyTable2′)

DBCC ind(@DBID, @TableID, -1)
GO

Here are the results:

 MS SQL generates a page (pagetype = 10) for an index and explains the page allocation details for an index. It shows the number of index page have been occupied for an index.

Now, let us view the page 26410 and 26412 and see the page details.

DBCC TRACEON (3604)
GO

DBCC page(10, 1, 26412, 3)
GO

Here are the results:

The output display many columns, but we are only interested in four of them as shown above.

This will display the output of the index page, which has got link to data page (ChildPageID).  EMPID is an index column will contain the starting row of the page.

Now, let us view the page 26410 and see the page details. 

DBCC TRACEON (3604)
GO

DBCC page (10, 1, 26410, 3)
GO

Here are the results:

Record Type = PRIMARY_RECORD     

EmpId            = 1

EmpName          = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

Record Type = PRIMARY_RECORD     

EmpId            = 2

EmpName          = bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

Record Type = PRIMARY_RECORD     

EmpId            = 3

EmpName          = cccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc

Though I have added disorder records, SQL has displayed the data page in sequence because we have got a clustered index on empid. This is absolutely great!  Adding a clustered index to the table has physically reordered the data pages, putting them in physical order based on the indexed column.