SQL Indexes: Clustered vs Non-Clustered
💡 Concept Name
SQL Indexes improve query performance by allowing fast lookup of rows. There are two main types: clustered and non-clustered indexes.
📘 Quick Intro
An index in SQL is like a lookup reference to quickly locate data in a table. Clustered indexes define the order of physical data; non-clustered indexes create a separate structure pointing to the data rows.
🧠 Analogy / Short Story
Imagine a library: A clustered index is like the books being arranged in alphabetical order directly on the shelves (the data is physically ordered). A non-clustered index is like a separate catalog that tells you which shelf and position a book is in—it's a shortcut to find the book faster, but the books themselves aren't reordered.
🔧 Technical Explanation
- 📦 Clustered Index: Determines the physical order of rows in a table (only one allowed per table).
- 📂 Non-Clustered Index: Separate structure that stores a sorted list of key values and pointers to data rows.
- 🚀 Improves performance for search, sort, filter, and join queries.
- 🔍 Used heavily in WHERE, ORDER BY, GROUP BY clauses.
- ⚖️ Index size, column selection, and usage frequency affect performance.
🎯 Purpose & Use Case
- ✅ Clustered: Use for primary keys or heavily sorted tables (e.g., transaction dates).
- ✅ Non-Clustered: Use for frequently searched columns not used in the clustered index.
- ✅ Combine with INCLUDE columns to optimize read performance.
- ✅ Use filters to create partial indexes on specific row subsets.
💻 Real Code Example
-- Clustered index on primary key
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY, -- Clustered index by default
Name NVARCHAR(100),
Department NVARCHAR(50)
);
-- Create a non-clustered index on Department
CREATE NONCLUSTERED INDEX IX_Dept ON Employees (Department);
-- Query using index
SELECT * FROM Employees WHERE Department = 'HR';
❓ Interview Q&A
Q1: What is a clustered index?
A: It sorts and stores the data rows in the table based on the indexed column.
Q2: How many clustered indexes can a table have?
A: Only one, since data can be sorted in only one way.
Q3: What is a non-clustered index?
A: It creates a separate structure with pointers to data rows.
Q4: Can a table have multiple non-clustered indexes?
A: Yes, you can create multiple non-clustered indexes per table.
Q5: Do indexes improve SELECT performance?
A: Yes, especially for large datasets with filtering or sorting.
Q6: When do indexes slow down performance?
A: During INSERT, UPDATE, or DELETE due to index maintenance.
Q7: What is index fragmentation?
A: It occurs when index pages become disorganized, affecting read speed.
Q8: How can you view existing indexes?
A: Use sys.indexes or tools like SSMS.
Q9: What’s a covering index?
A: An index that includes all columns required by a query, avoiding table lookups.
Q10: Should small tables be indexed?
A: Not always necessary; the overhead may outweigh benefits.
📝 MCQs
Q1. Which index determines the physical order of rows?
- Non-clustered index
- Hash index
- Clustered index
- XML index
Q2. How many clustered indexes can a table have?
- Zero
- One
- Two
- Unlimited
Q3. What does a non-clustered index store?
- Full table
- Only keys
- Pointers to data rows
- Sorted table copy
Q4. What SQL command creates an index?
- ADD INDEX
- CREATE INDEX
- NEW INDEX
- MAKE INDEX
Q5. Which index type improves ORDER BY queries most?
- Full text
- Non-clustered
- Clustered
- Spatial
Q6. What’s a disadvantage of too many indexes?
- More joins
- Slower select
- Slower insert/update/delete operations
- Cannot query
Q7. What’s used to view indexes in SQL Server?
- index_view()
- get_indexes()
- sys.indexes
- db.indexes
Q8. What is a covering index?
- Indexes only on keys
- Backup index
- Index that includes all query columns
- Secondary index
Q9. Can indexes be created on multiple columns?
- No
- Yes
- Only 2 columns
- Only if clustered
Q10. What is index fragmentation?
- Unindexed data
- Null values
- Disorganized index pages
- Deleted rows
💡 Bonus Insight
Index choice is critical for large-scale systems. Use INCLUDE columns in non-clustered indexes to create covering indexes, reducing lookups and improving performance. Periodically rebuild or reorganize indexes to fix fragmentation.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!