Clustered versus non-clustered indexes

WHAT THIS TESTS The interviewer checks whether you understand how each index type relates to the physical layout of table data.

A GOOD ANSWER COVERS A clustered index defines the physical order in which the table's rows are stored on disk; the table's data pages are themselves the leaf level of this index, sorted by the clustered key. Because rows can only be physically ordered one way, a table can have at most one clustered index, often on the primary key. A non-clustered index is a separate structure that stores the indexed column values in sorted order along with a pointer to each row, either a row locator or the clustered-index key. A table can have many non-clustered indexes. Looking up via a non-clustered index finds the indexed value, then often performs an extra step, a key lookup or bookmark lookup, to retrieve the remaining columns from the table, unless the index is covering and already contains every needed column.

COMMON WRONG ANSWERS Saying a table can have multiple clustered indexes; only one is possible because data has a single physical order. Thinking a non-clustered index reorders the table; it does not, it is a separate structure. Forgetting the extra lookup cost of non-clustered indexes for non-covered columns.

LIKELY FOLLOW-UPS What is a covering index and how does it avoid the extra lookup? How do heap tables differ when no clustered index exists? Why does the clustered key affect non-clustered index size? How does this differ across engines like InnoDB and SQL Server?

ONE CONCRETE EXAMPLE With a clustered index on user_id, rows are physically stored in user_id order, so a range scan of consecutive IDs reads sequential pages. A non-clustered index on email finds the matching entry, then follows its pointer to fetch the full row from wherever it physically sits.