SQL Server – Index Structure

By Navratan Verma

Let us see how is index structure stored in SQL Server, this demo uses SQL Server 2014.
Demo Script IndexStructure

Create the Database and Table.

IndexDatabase

Create clustered index, use DBCC Traceon(3604) to display output to window.
Then use undocumented command (SEMETADATA) to list pages for the table

IdxDemo2

Output from the above command, identify the root page ID from the field HoBtRoot

IdxDemo3

Display Root Page Info from DBCC Page

IdxDemo4

Pick one of the page to display its contents ex: 288 here, using DBCC Page.

IdxDemo5

You can also see actual page data stored in the leaf level page, in the above command.

Leave a Reply

Your email address will not be published. Required fields are marked *