What is the difference between clustered and non-clustered indexes?
A clustered index determines the physical storage order of rows in the table. The leaf nodes of a clustered B-tree are the actual data rows themselves -- the index and the table are one structure. Because there is only one physical ordering, a table can have only one clustered index. In SQL Server and MySQL (InnoDB), the primary key is the clustered index by default. Range queries and ORDER BY on the clustered key are extremely efficient because the rows are already sorted on disk -- sequential I/O rather than random I/O.
A non-clustered index is a separate B-tree structure that stores copies of the indexed column values as keys and, in each leaf node, a pointer back to the actual row. In InnoDB this pointer is the primary key value (not a direct disk address); in SQL Server it is a row identifier (RID) for heap tables. A table can have many non-clustered indexes. When the query engine uses a non-clustered index, it first traverses the index B-tree to find the matching keys, then follows the row pointers to fetch the full rows -- a two-step lookup called a key lookup or bookmark lookup. This extra hop adds cost for non-covering queries.
The performance implications are concrete. A clustered index lookup is a single B-tree traversal ending at the data row. A non-clustered index lookup is a B-tree traversal plus an additional random I/O to fetch each row -- costly when many rows match. A non-clustered index that covers a query (contains all requested columns) eliminates the second lookup, performing as well as a clustered lookup for that specific query.
Design guidance: choose the clustered key carefully -- once set, reordering physical rows is expensive. High-cardinality, ever-increasing keys (like auto-increment IDs) minimize page splits and fragmentation. UUIDs as clustered keys cause fragmentation because random inserts scatter across the entire B-tree.
| Aspect | Clustered Index | Non-Clustered Index |
|---|---|---|
| Physical storage | Rows stored in index order | Separate structure; rows stored elsewhere |
| Quantity per table | One only | Many (typically up to 999 in SQL Server) |
| Leaf nodes contain | Actual data rows | Index key + row pointer (or primary key) |
| Range query performance | Excellent -- sequential I/O | Good if covering; otherwise random I/O per row |
| Lookup cost | Single B-tree traversal | B-tree traversal + key lookup if non-covering |
| Insert cost | Rows inserted in sorted position; risk of page splits | Index updated separately; smaller overhead |
| Default in InnoDB/SQL Server | Primary key | Explicitly created indexes |
Candidate knows there is one clustered index that determines physical order and that non-clustered indexes are separate with pointers, but cannot explain the two-step lookup, covering indexes, or the page-split risk with random clustered keys.
Candidate explains physical vs logical ordering, the two-step lookup for non-clustered indexes, covering index optimization, page-split risk with UUID clustered keys, and gives accurate count constraints.
Reading the answer is step one. Explaining it unprompted — under interview pressure — is what actually matters. Get AI-graded feedback on your answer with follow-up probes on your weak points.
Get Graded — Free Assessment