A non-clustered index is a database index stored separately from the table data, consisting of sorted index keys and a row locator that points to the full row, so that queries can find rows quickly without the table itself being ordered by those keys. In indexing for relational databases, it is the most common way to support searches, joins and sorting on columns other than the one that defines the physical order of a table.
Because the index and the data are separate, a table can have many non-clustered indexes but at most one clustered index. The term is used mostly in SQL Server and related systems; in other engines the same idea is called a secondary index.
How a non-clustered index locates rows
Most non-clustered indexes are B-tree structures. The root and intermediate levels guide the search, and the leaf level holds one entry per row: the indexed key values plus a pointer to the row. A lookup descends a few levels, typically three or four even for tables with millions of rows, then follows the pointer to the data.
What the pointer contains depends on how the table is stored. If the table has a clustered index, the pointer is the clustered index key; if the table is a heap without a clustered index, it is a physical row identifier naming the file, page and slot. Following the pointer is called a key lookup or bookmark lookup, and it is the main cost of a non-clustered index.
The table below shows how the row locator differs between storage models.
| Table storage | Row locator in leaf entries | Consequence |
|---|---|---|
| Clustered table | Clustered index key | Wide clustered keys enlarge every non-clustered index |
| Heap table | Physical row identifier | Direct access, but forwarded records add extra reads |
| InnoDB secondary index | Primary key value | Every lookup traverses the primary key B-tree |
| PostgreSQL index | Tuple identifier in the heap | All indexes are secondary to the heap |
Key parameters and design options
A non-clustered index is defined by more than its key column. Column order, included columns, filters and fill factor all affect which queries it helps and how much it costs to maintain. The following options are available in most major engines, sometimes under different names.
| Option | Effect | Typical guidance |
|---|---|---|
| Key column order | Defines which predicates can seek | Put equality columns before range columns |
| Included columns | Stored only at leaf level, not in the key | Add to avoid key lookups for frequent queries |
| Filter or partial condition | Indexes only rows matching a predicate | Useful for sparse values or active subsets |
| Uniqueness | Enforces distinct key values | Gives the optimizer stronger guarantees |
| Fill factor | Free space left on leaf pages | Lower values reduce page splits on busy inserts |
Engines impose limits. SQL Server allows up to 999 non-clustered indexes per table and a maximum key size of 1,700 bytes, although practical designs stay far below both. When an index contains every column a query needs, it becomes a covering index, and the query can be answered from the index alone.
Non-clustered versus clustered and other index types
The fundamental difference is that a clustered index is the table, with data rows stored in key order at its leaf level, whereas a non-clustered index is an additional copy of selected columns that refers back to the table. This difference drives their strengths and costs.
| Property | Clustered index | Non-clustered index |
|---|---|---|
| Leaf level contains | Full data rows | Keys, included columns and row locators |
| Number per table | One | Many |
| Range scans | Very efficient, data is contiguous | Efficient if covering, otherwise many lookups |
| Extra storage | None beyond the table | Proportional to key and included columns |
| Effect on writes | Determines insert order and page splits | Each index adds maintenance to every write |
Other index types can also be non-clustered. Hash indexes support only equality lookups, bitmap indexes suit low-cardinality columns in analytical workloads, and full-text indexes handle word searches. The B-tree non-clustered index remains the general-purpose default for transactional systems.
When the optimizer uses a non-clustered index
The query planner chooses a non-clustered index only when it expects the index to be cheaper than scanning the table. The decisive factor is index selectivity: an index seek followed by key lookups is attractive when a query returns a small fraction of the rows, often well under a few percent, and becomes slower than a full scan as that fraction grows.
The tipping point depends on row width, page count, storage speed and whether the index covers the query. A covering index avoids lookups entirely, which is why adding a few included columns frequently turns an ignored index into the preferred access path.
| Situation | Likely plan | Reason |
|---|---|---|
| Highly selective predicate on key | Index seek plus key lookups | Few rows, few random reads |
| Query covered by the index | Index seek or scan only | No access to the base table |
| Low selectivity predicate | Table or clustered index scan | Lookups would cost more than scanning |
| Leading key column not filtered | Index scan or no index use | B-tree cannot seek on a later column |
Costs and common mistakes
Every non-clustered index must be updated on each insert, delete and relevant update, so indexes trade write speed and storage for read speed. On write-heavy tables, a large number of indexes can noticeably increase latency and lock contention.
- Duplicate or overlapping indexes, such as one on (A) and another on (A, B), which add cost without benefit.
- Wrong column order, which prevents seeks for the most common queries.
- Functions on indexed columns in predicates, which usually make the index unusable unless an expression index exists.
- Never reviewing usage statistics, so unused indexes keep slowing writes for years.
The idea in brief
Think of the index at the back of a textbook. The book itself is ordered by chapters, but the index lists topics alphabetically with page numbers. A non-clustered index works the same way: it is a separate, sorted list that tells the database exactly where to find each row.
A book can have several such indexes, for names, places and subjects, and each one makes a different kind of search fast. The price is extra pages to print and update whenever the content changes.
A practical scenario
An order management system stores about 40 million orders in a table clustered by order number. Customer service staff search constantly by customer and date range, and each search scans the whole table, taking several seconds during peak hours.
The database team creates a non-clustered index on customer number and order date, with the order status and total as included columns. The typical query is now fully covered by the index, so the planner performs an index seek that reads only a handful of pages. Search time drops to a few milliseconds, while insert performance on the table changes only slightly because one additional index is maintained.