Chapter 2: SQL Clustered Index Storage

In previous article we have seen how sql clustered index will reorder the data and creates the index pages. Incase if you not read, i would recommend to go through page.

In this article we will learn how Clustered indexes are stored.Clustered indexes pages stores in B-tree structre.

For demo purpose i have created a table with 1000 records and Id as primary key. When we run the following script we get all pages.

DBCC IND('databasename','tablename',-1)

In above diagram we can clearly see the each data pages(page types -1) is connected in a linked list manner.Each page knows what is it’s previous & next pages to perform better search.

In index pages (page type -2) the clustered index ranges are stored with pageid. To check this run the following script.

DBCC traceon(3604)
DBCC PAGE ('test', 1, 32992, 3) 

In the above digram , the child key ranges stored in id(key) and respective child page id. When we search for id = 300, the sql will look into the cluster index page which page it is stored, then in that page it will search for exact match in Page Row_Offset(refer the sql page to understand).