How do clustered and non-clustered indexes differ?
This question evaluates understanding of physical storage and query performance tuning.
Why Interviewers Ask This
Indexes are critical for speed. Interviewers want to know if you understand how data is physically ordered. Choosing the wrong index type can cripple performance.
How to Answer This Question
Explain clustered index stores rows in key order (only one per table). Non-clustered is a separate structure pointing to rows (many allowed). Discuss use cases: range scans vs point lookups.
Key Points to Cover
- Clustered = physical order
- One clustered index per table
- Non-clustered = separate structure
- Use cases for each
Sample Answer
A clustered index stores table rows in the physical order of the index key, meaning the data pages are the index. You can have only one, ideal for range scans. A non-clustered index is a separate structure mapping keys to row locators, allowing many indexes for fast point lookups or specific filters.
Common Mistakes to Avoid
- Thinking you can have multiple clustered indexes
- Confusing covering indexes with non-clustered
- Ignoring physical storage implications
Practice This Question with AI
Answer this question orally or via text and get instant AI-powered feedback on your response quality, structure, and delivery.
Related Interview Questions
Describe a PRIMARY KEY and how it differs from a UNIQUE key
Medium
What is ER model in the DBMS?
Medium
FlipkartWhat is the difference between LIKE and equals operators in SQL?
Easy
TCSWhat is Join and explain its types?
Medium
FlipkartWhat is the difference between UNION and UNION ALL?
Easy
What is the ER model in DBMS?
Easy
Flipkart