nonclustered

A nonclustered index is a database index that provides fast access to data without altering the physical order of the data rows in a table. It stores a separate structure containing index keys and pointers to the corresponding data rows, allowing quick lookups, range scans, and joins based on the indexed columns.

In contrast to a clustered index, which determines the physical order of data within a table, a table can have only one clustered index (or be a heap with no clustering). A nonclustered index remains separate from the data and references the rows either directly through a row locator or, in the case of a clustered table, via the clustering key.

The typical structure of a nonclustered index includes the indexed column values and a pointer to the actual data. For a table with a clustered index, the pointer is often the clustering key values; for a heap, the pointer is a row identifier. Nonclustered indexes can be used to satisfy queries, enforce unique constraints, and improve performance for lookups, range queries, and certain joins. They can also be designed as covering indexes when they include all the columns needed by a query, potentially avoiding additional data lookups.

Maintenance and storage considerations are important: nonclustered indexes require extra space and must be updated on insert, update, and delete operations, which can slow writes. They are typically designed selectively for columns with high query frequency and good selectivity. Many systems support features such as include columns to cover queries, filtered indexes for selective data, and multiple nonclustered indexes per table, each tailored to different query patterns.