What are Clustered and Non-Clustered Indexes in SQL, and when should you use each?
Quick Revision Summary
An Index in a relational database speeds up data retrieval operations at the cost of additional write storage and update overhead. Clustered Index Determines the physical storag...
Interactive Practice Mode
Formulate your thoughts or take notes before revealing the model solution.
Saved automatically
Model Answer & Deep-Dive Explanation
An Index in a relational database speeds up data retrieval operations at the cost of additional write storage and update overhead.
Clustered Index
- Determines the physical storage order of rows on disk.
- A table can have only one clustered index (usually the PRIMARY KEY).
- Leaves of the index B-tree contain the actual table data pages.
Non-Clustered Index
- Maintains an independent structure from physical table data rows.
- Contains key values pointing to the clustered index key or row ID (RID).
- A table can have multiple non-clustered indexes (e.g. on
email,created_at, or foreign keys).
Interviewer Evaluation Points & Scoring Rubric
- 1 Structure answer clearly: Core concept, Practical example, and Pitfalls to avoid.
- 2 Demonstrate clear communication without unnecessary buzzwords.
- 3 Relate concept to real-world software development scenarios.
Related Questions in SQL & Relational Databases
Explain Redis In-Memory Data Structures and Common Caching Patterns (Cache-Aside, Write-Through, Write-Behind).
Intermediate level • 63 views
How do INNER JOIN and LEFT JOIN differ when a customer has no orders?
Easy level • 58 views
A LEFT JOIN report counts one order for customers who have none. What caused it?
Medium level • 57 views
Explain SQL ACID properties and the differences between READ COMMITTED and REPEATABLE READ isolation levels.
Intermediate level • 54 views
How would you choose a composite index for a filtered, ordered query?
Medium level • 54 views