Mistake: treating every index operation as a scan. A clustered index seek traverses the clustered index’s B-tree to locate a matching row or key range, usually reading far fewer pages than scanning the table’s leaf level. section 4.2
SalesOrderHeaders lookup
A single-row lookup on SalesOrderHeaders can seek from the root through intermediate levels to the target data page. For multiple matching rows, the seek finds the starting point, then reads the relevant leaf-level pages. section 4.2
Keep these rules in mind:
- A clustered index stores the table’s data pages at its leaf level, so a table can have only one clustered index. section 4.2
- A seek’s single-row page-read maximum follows the B-tree levels; a scan may read the table’s leaf data pages. section 4.2
Figure 4.6 Clustered index seeks and scans against a B-tree
Use the B-tree illustration to compare how seeks descend through index levels with how scans search leaf pages. section 4.2
