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 SQL ACID properties and the differences between READ COMMITTED and REPEATABLE READ isolation levels.
Intermediate level • 1 views
Explain Redis In-Memory Data Structures and Common Caching Patterns (Cache-Aside, Write-Through, Write-Behind).
Intermediate level • 1 views
What is Database Indexing, how does a B+ Tree index work, and when should you avoid creating indexes?
Intermediate level • 1 views
Explain Common Table Expressions (CTEs) and SQL Window Functions (ROW_NUMBER, RANK, DENSE_RANK) with use cases.
Intermediate level • 1 views
Explain Database Normalization from 1NF through BCNF with real-world anomaly examples.
Intermediate level • 1 views