Microsoft SQL Server 2012 Internals

Physical index structures for B-trees

Index pages for B-tree indexes have almost the same structure as data pages, except they store index rows instead of data rows. Like with all other types of pages in SQL Server, index pages use a fixed size of 8 KB, or 8,192 bytes. Index pages also have a 96-byte header, and an offset array at the end of the page has 2 bytes for each row to indicate the offset of that row on the page. A nonclustered index can have all three allocation units associated with it: IN_ROW_DATA, ROW_OVERFLOW_DATA, and LOB_DATA, as mentioned in Chapter 6 and discussed in detail in Chapter 8. Each index has a row in the sys.indexes catalog view, with an index_id value of either 1 (for a clustered index) or a number between 2 and 250 ...