Difference Between Clustered Index and Non-Clustered Index in SQL
💡 Concept Name
Clustered Index vs Non-Clustered Index – Two types of indexing mechanisms used to speed up data retrieval in SQL databases.
📘 Quick Intro
A clustered index determines the physical order of data in a table. A non-clustered index creates a separate structure to point to data rows without changing their physical order.
🧠 Analogy / Short Story
Imagine a library where books are physically arranged by the author's last name (clustered index), making it quick to find books by author. A non-clustered index is like an index card catalog listing book titles with page references, pointing to where the books are physically located.
🔧 Technical Explanation
- Clustered index sorts and stores the actual table rows in order based on the indexed column(s).
- Each table can have only one clustered index because rows can be sorted physically in only one order.
- Non-clustered index creates a separate structure with pointers to the data rows.
- Tables can have multiple non-clustered indexes.
- Clustered index improves range queries due to physical order.
- Non-clustered index is useful for fast lookups on columns not defining row order.
💻 Code Examples
-- Creating clustered index
CREATE CLUSTERED INDEX IX_Employees_LastName
ON Employees(LastName);
-- Creating non-clustered index
CREATE NONCLUSTERED INDEX IX_Employees_Department
ON Employees(DepartmentID);
❓ Interview Q&A
Q1: What is a clustered index?
A: It sorts and stores the actual table data rows in order based on the indexed column.
Q2: How many clustered indexes can a table have?
A: Only one.
Q3: What is a non-clustered index?
A: A separate data structure that stores pointers to the data rows.
Q4: Can a table have multiple non-clustered indexes?
A: Yes.
Q5: Which index improves range queries better?
A: Clustered index.
Q6: Does a clustered index change the physical order of rows?
A: Yes.
Q7: Is a non-clustered index stored physically in order?
A: No, it maintains a separate structure.
Q8: When should you use a clustered index?
A: When you frequently query ranges or order by the indexed column.
Q9: Can non-clustered indexes improve performance on search columns?
A: Yes.
Q10: Do indexes affect write performance?
A: Yes, they can slow down inserts and updates.
📝 MCQs
Q1. What does a clustered index do?
- Creates separate pointers
- Sorts and stores data rows physically
- Deletes duplicates
- Optimizes backups
Q2. How many clustered indexes can a table have?
- One
- Multiple
- None
- Two
Q3. What is a non-clustered index?
- Sorted data rows
- Separate structure with pointers
- Backup
- Transaction log
Q4. Can a table have multiple non-clustered indexes?
- No
- Yes
- Only one
- Depends on DBMS
Q5. Which index is better for range queries?
- Non-clustered index
- Clustered index
- Both same
- None
Q6. Does clustered index change physical order?
- No
- Yes
- Sometimes
- Never
Q7. Is non-clustered index stored physically in order?
- Yes
- No
- Sometimes
- Always
Q8. When to use clustered index?
- For exact matches only
- For range queries
- Never
- For deletes
Q9. Do indexes affect write performance?
- No
- Yes
- Depends
- Only clustered indexes
Q10. Can non-clustered indexes speed up searches?
- No
- Yes
- Sometimes
- Only with clustered index
💡 Bonus Insight
Choosing the right type of index based on your query patterns can drastically improve database performance. While clustered indexes define the physical storage order, non-clustered indexes provide flexible lookup paths.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!