A clustered index stores a table’s data pages in the order of its clustered key. It is a B-tree with one root page, optional intermediate levels, and leaf-level pages that contain the actual table data. Because the data pages themselves are ordered, a table can have only one clustered index. section 4.2 section 11.0
Mistake #12: using a wide primary key. Making a long character order number the primary key can seem natural, but the primary key normally becomes the clustered index. A 12-character NCHAR value uses 24 bytes per row, compared with 4 bytes for INT or 8 bytes for BIGINT, so key choice can add substantial storage overhead. section 4.2
The B-tree makes targeted reads efficient. A clustered index seek follows index levels to locate a record, reading at most one page per B-tree level for a single-row lookup. A clustered index scan instead searches leaf-level data pages, potentially reading every data page in the table. For multiple matching rows, a seek can locate the starting point before leaf pages are read. section 4.2
Choose the clustered key with these rules in mind:
- Prefer the smallest practical data type when a key will be clustered. section 4.2
- Do not assume a primary key must be the clustered key; a different unique column can be used when the primary key is wide. section 11.0
- Treat a heap differently: without a clustered index, pages have no particular order and many SELECT queries may need to read every data page. section 11.0
Sales order lookup
Suppose SalesOrderHeaders uses an NCHAR(12) order number such as ABC1234D-E12 as its primary key. If that key becomes clustered, every row carries the larger key value through the clustered structure. Where business rules allow, a smaller clustered key can reduce that cost while leaving the long order number available as a separate unique value. section 4.2
Figure 4.6 Clustered index seeks and scans against a B-tree
Use the B-tree diagram to compare a seek descending through index levels with a scan reading leaf-level data pages. section 4.2
