Nonclustered index
A nonclustered index is a separate database structure that stores keys and row locators without determining a table’s physical row order.
100 SQL Server Mistakes and How to Avoid Them
A nonclustered index is a separate index structure that stores keys and row locators without determining the table's physical row order.
- How it is used
- Speeds joins, filters, and aggregations while leaving table data pages unordered.
- How deep it goes
- Defines B-tree leaf locators, included values, clustered-key replication, and bulk-load maintenance, then demonstrates page inspection.
- Examples
- Uses SalesOrderDate and foreign-key indexes, index-page DBCC inspection, and the ImpressionArchive bulk-load scenario.
- What you can do after
- Operate/configure it by selecting query-supporting indexes and disabling then rebuilding nonclustered indexes around large bulk loads.
Read how 100 SQL Server Mistakes and How to Avoid Them explains nonclustered index →
Learn SQL in a Month of Lunches
A nonclustered index is a separate database object that stores an ordering or lookup path for table rows without defining the table's physical order.
- How it is used
- Speeds commonly used queries by providing lookup paths that find table rows without reading the entire table.
- How deep it goes
- Explains separate ownership, lookup behavior, examples, and write costs, but does not detail index internals or optimizer decisions.
- Examples
- Demonstrates library catalogs, shipment tracking numbers, and a ShipmentDate index supporting a grouped count.
- What you can do after
- Apply it via SQL by creating focused indexes for columns used in selective queries.
Read how Learn SQL in a Month of Lunches explains nonclustered index →
Related concepts
- indexingkind of · Indexing is the organization or use of positions, keys, or lookup structures to identify and retrieve selected data or elements.
- lookup operationused by · A lookup operation locates or retrieves data associated with a specified key, identifier, index, or other locating value.
- clustered indexcontrasts with · A clustered index is a database index whose key determines the physical order of rows in a table or indexed view.
- unique constraintproduced by · A data-model rule requiring designated values or combinations of values to distinguish the records or entities to which the rule applies.
These guides are generated from the text of Manning books by Manning's knowledge graph and link to the sections they draw on. The full books are available on liveBook and at manning.com.