Non Clustered Index

A separate structure pointing back to the real data

A non-clustered index is a distinct data structure, separate from the table's actual physical row storage. It holds the indexed column's values (sorted, typically as a B+ Tree) plus, for each value, a pointer/reference back to where the actual full row lives — either in the clustered index (if one exists) or in the raw heap storage (if the table has no clustered index).

Imagine students is clustered on id, and you add a non-clustered index on department. The non-clustered index is a separate B+ Tree whose leaves hold department values in sorted order, each paired with a reference back to the corresponding row's location (often the clustered key value, e.g., id).

The lookup has an extra "hop"

Consider:

sql
SELECT * FROM students WHERE department = 'Computer Science';

The engine first searches the non-clustered index's B+ Tree to find matching department entries — this gives it a list of pointers (e.g., id values). It then must take a second step: using each pointer, it looks up the actual row (via the clustered index, or heap location) to retrieve the other columns (name, gpa, email). This two-step process (index lookup, then row lookup) is often called a "bookmark lookup" or "key lookup," and it's inherently one extra hop compared to a clustered index's single-step access.

Why you'd want many of them

Since a table can have only one clustered index (20.5), but real applications query on many different columns, non-clustered indexes fill that gap: a table can have many non-clustered indexes — one on department, another on gpa, another on email, etc. — each optimized for a different query pattern, without touching the table's physical storage order.

Edge cases

• If the extra "hop" to fetch full row data is expensive (e.g., a query needs many columns not in the index), a covering index (20.8) can eliminate that second step entirely by including all needed columns directly in the non-clustered index itself.
• Every non-clustered index adds its own write overhead (20.14) — a table with 10 non-clustered indexes means every INSERT must update 10 separate structures in addition to the table itself.
• On a heap table (no clustered index at all), the non-clustered index's pointer is typically a direct physical row identifier rather than a clustered key — the exact mechanism is engine-specific.

Interview-style Q&A

Q: What's the key structural difference between a clustered and a non-clustered index? A: A clustered index physically IS the row-ordered table data; a non-clustered index is a separate structure that stores indexed values plus a pointer back to the actual row stored elsewhere.

Q: Why can a table have many non-clustered indexes but only one clustered index? A: Because non-clustered indexes are independent side structures that don't affect physical row order, so you can build as many as you want; the clustered index dictates the one-and-only physical row order.

Key takeaway: a non-clustered index trades one extra lookup "hop" (from index entry to actual row) for the flexibility of supporting many different query patterns on the same table simultaneously.