When you select a row using a nonclustered index, the search starts at the root level. sysindexes.root stores the page number for the root page of the nonclustered index.
In Figure 9-8, “Green” is greater than “Bennet,” but less than “Karsen,” so the pointer to page 1007 is followed.
“Green” is greater than “Greane,” but less than “Hunter,” so the pointer to page 1133 is followed. Page 1133 is the leaf page, showing that the row for “Green” is row 2 on page 1421. This page is fetched, the “2” byte in the offset table is checked, and the row is returned from the byte position on the data page.
Figure 9-8: Selecting rows using a nonclustered index