What is the difference between a clustered and a non-clustered index?
Assesses fundamental understanding of DBMS conventions, runtime behavior, and memory/performance considerations.
Hiring managers look for precision, avoidance of ambiguous jargon, and ability to explain trade-offs under real production conditions.
A clustered index defines the physical order of rows in the table. Because the data is stored in that order, a table can have only one clustered index, usually the primary key when the engine supports clustered storage, as SQL Server does. Range scans and ordered retrieval along the key are fast because rows are adjacent.
A non-clustered index is a separate structure that stores the indexed key plus a pointer back to the row. A table can have many. A lookup may require an extra hop, called a key lookup, to fetch remaining columns. If the index includes all queried columns, it covers the query and avoids that hop.
CREATE CLUSTERED INDEX ix_orders_date ON orders(order_date);
CREATE INDEX ix_orders_customer ON orders(customer_id) INCLUDE (total);
Choose a clustered key that is narrow, unique, stable and ever-increasing, like an identity, to avoid page splits. Wide or random clustered keys such as UUIDs can cause fragmentation and poor insert performance.
Candidate Response Strategy & Interview Tips
- Start with a concise one-sentence summary: Deliver a direct, confident answer first before expanding into nuances.
- Demonstrate real-world trade-offs: Discuss where this approach excels and when you would avoid it in production systems.
- Discuss complexity & edge cases: Proactively explain time/space complexity or boundary conditions (null values, scale limits).
- Prepare for interviewer follow-ups: Technical hiring panels frequently probe deeper into concurrency, backward compatibility, or alternative libraries.