Explain the difference between a clustered and a non-clustered index.
Instruction: Describe the distinctions between clustered and non-clustered indexes and their impact on database performance.
Updated
Example Answer
I would identify the database engine first because “clustered” is not implemented the same way everywhere. In SQL Server rowstore tables, a clustered index’s leaf level contains the table’s data rows in logical key order. A nonclustered index is a separate structure whose leaf entries include keys and a row locator, plus any included columns. The locator is the clustered key for a clustered table or a row identifier for a heap.
This does not mean rows occupy one physically contiguous disk region, and an index does not guarantee a SELECT result order without ORDER BY. PostgreSQL’s CLUSTER command is different: it physically reorders a table once using an index, but later writes do not maintain that order automatically. I would select indexes from actual lookup and range-query patterns, and account for write overhead, storage, and the cost of a wide clustered key.
Why this works
The explanation gives a concrete engine’s behavior and prevents a familiar SQL Server concept from becoming a false universal rule.
Follow-up to prepare for
Would including selected columns make a nonclustered index cover the query, and what maintenance cost would that add?