liveBook · Manning

Index page

As explained in 100 SQL Server Mistakes and How to Avoid Them

An index page is a fixed-size database page containing part of an index structure and its keys or pointers.

An index page is a fixed-size database page containing part of an index structure and its keys or pointers. In a clustered-index B-tree, pages form levels: upper index pages point to lower pages, while the leaf level contains the table’s data pages. section 4.2

Keep these distinctions clear:

  • A clustered index has one root page, zero or more intermediate levels, and a leaf level made from the table’s data pages. section 4.2
  • A clustered index seek traverses the relevant index pages; a scan searches the leaf-level data pages. section 4.2
  • A wide key consumes space in index pages and can increase the storage cost of the index structure. section 4.2

Inspecting pages in SalesOrderHeaders

Find an index ID with sys.indexes, discover its page numbers with DBCC IND, then inspect a selected page using DBCC PAGE after enabling trace flag 3604.

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes
Works through B-tree levels, page traversal, and diagnostic commands, but defers fuller index treatment.
Examples
Uses SalesOrderHeaders, sys.indexes, DBCC IND, DBCC PAGE, and a clustered-index seek-versus-scan diagram.
What it assumes
Assumes familiarity with tables and primary keys while introducing clustered-index structure and page-level navigation.
What you can do after
Diagnose index-page structure by identifying an index, locating its pages, and viewing page contents with SQL Server diagnostics.
How it connects
Connects index pages to clustered indexes, B-trees, data pages, index seeks, index scans, and primary keys.
Cover of 100 SQL Server Mistakes and How to Avoid Them
liveBookmanning.com
Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.