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.


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


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


Display Root Page Info from DBCC Page


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


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 *