Two Kinds
A clustered index determines the physical order of rows on disk: the table data itself is stored in the index leaves, sorted by the index key. There can be only one per table, because rows can only be laid out one way.
A nonclustered index is a separate structure. Its leaves hold the indexed key plus a pointer back to the actual row, leaving the table order untouched. A table can have many of these.
What Changes
- A clustered lookup lands directly on the row, since the row lives in the leaf.
- A nonclustered lookup finds the pointer, then does an extra fetch to read the row, sometimes called a bookmark or heap lookup.
Picking The Clustered Key
Because it sets physical order, the clustered key shapes range performance. A good choice is queried in ranges and is monotonic, like an id or timestamp, so inserts append rather than scatter. A random clustered key causes page splits and fragmentation.
Key idea
A clustered index stores the table rows in key order so there is one per table, while a nonclustered index is a separate pointer structure that needs an extra fetch, so a table can have many.