A nonclustered index is a separate B-tree built on one or more table columns. Its leaf level stores row locators rather than the table’s full data pages, so it can speed joins, filters, and aggregations without changing the physical order of table data. A table can therefore have multiple nonclustered indexes. section 11.0 section 4.2
The locator points to a heap or to a clustered index. When a clustered index exists, its key is copied into every nonclustered index locator; a wide clustered or primary key is therefore replicated widely. INCLUDE columns are an exception to the pointer-only idea: their values are stored in the nonclustered index alongside the locator. section 4.2 section 4.2
Use a nonclustered index deliberately:
- Index columns frequently used to filter sales orders, such as an order date. section 4.2
- Index foreign-key columns used in joins, such as SalesAreaID and CustomerID, to support more efficient join operations. section 4.2
- Balance read improvements against disk or memory consumption and slower writes; up to 256 nonclustered indexes are supported, but excess indexes add maintenance work. section 11.0
Bulk-load trap
Loading millions of rows into an archive table with reporting indexes updates both table pages and every nonclustered index. The extra I/O, transaction-log work, page splits, and fragmentation can make the load substantially slower. For a large load, disable the nonclustered indexes, insert the rows, then rebuild them; do not disable the clustered index, because doing so makes the table inaccessible. section 11.9 section 11.9
Use the insertion diagram to see why every added row creates work in the table and in each separate nonclustered index. section 11.9
Inspecting an index
Find a named index and its index_id in sys.indexes, then use DBCC IND to locate its pages. DBCC PAGE can inspect a selected page after enabling trace output, making the separate index structure and its stored entries tangible. section 4.2 section 4.2
