📚 The Phone Book vs The Library Catalog
Clustered indexes physically order data (like phone book). Non-clustered indexes point to data (like library catalog). Choose wisely.
📖 Clustered Index
- Only one per table
- Physically reorders data
- Fast for range queries (BETWEEN, >, <)
- Primary key = clustered by default
- Slow for inserts (data rearrangement)
🏷️ Non-Clustered Index
- Up to 999 per table
- Separate structure pointing to data
- Fast for exact matches (=, IN)
- Includes key lookup (extra step)
- Can INCLUDE additional columns
📝 Creating Indexes
-- Clustered (Primary Key)
CREATE TABLE Users (
Id INT PRIMARY KEY CLUSTERED,
Name NVARCHAR(100)
);
-- Non-clustered on Name
CREATE NONCLUSTERED INDEX IX_Users_Name ON Users(Name);
-- Composite index (multiple columns)
CREATE INDEX IX_Orders_UserDate ON Orders(UserId, OrderDate);
-- Included columns (cover query)
CREATE INDEX IX_Orders_UserDate
ON Orders(UserId, OrderDate)
INCLUDE (TotalAmount, Status);
-- Unique non-clustered
CREATE UNIQUE INDEX IX_Users_Email ON Users(Email);
✅ When to Use Which
- Clustered: Primary key, columns with range queries, columns with unique values
- Non-clustered: Foreign keys, columns in WHERE/JOIN, search columns
- Composite: When filtering on multiple columns together
- INCLUDE: Cover queries to avoid key lookups
💡 Best Practices
- Choose clustered key wisely (narrow, unique, ever-increasing)
- Don’t over-index (each index slows writes)
- Drop unused indexes (check usage stats)
- Consider index order (most selective columns first)
- Regularly rebuild/reorganize indexes
“Query was taking 45 seconds. Added one non-clustered index on the WHERE column. Query dropped to 0.2 seconds. Indexing is the single biggest performance lever you have.”

