Indexes are the biggest performance booster in SQL Server—and also the easiest way to destroy performance if used wrongly.
This blog explains how indexes work, which type to use, and how to design indexes for stored procedures.
1️⃣ What Is an Index in SQL Server?
An index is a data structure that helps SQL Server find rows faster, similar to an index in a book.
📌 Without index → SQL Server scans entire table
📌 With index → SQL Server seeks directly to data
2️⃣ How Indexes Improve Stored Procedure Performance
❌ Without Index
- Table Scan
- High CPU & IO
- Slow response
✅ With Index
- Index Seek
- Less IO
- Faster execution
Indexes directly affect:
✔ Execution plans
✔ Query cost
✔ Application speed
3️⃣ Types of Indexes in SQL Server
🟢 Clustered Index
- Defines physical order of table
- Only one per table
- Usually on Primary Key
CREATE CLUSTERED INDEX IX_Users_UserId
ON Users(UserId);
🔵 Non-Clustered Index
- Separate structure
- Multiple allowed
CREATE NONCLUSTERED INDEX IX_Users_Email
ON Users(Email);
🟣 Composite Index
- Index on multiple columns
- Order matters
CREATE INDEX IX_Orders_UserDate
ON Orders(UserId, OrderDate);
🟡 Covering Index (INCLUDE)
- Avoids key lookups
- Improves performance
CREATE INDEX IX_Orders_UserId
ON Orders(UserId)
INCLUDE (OrderDate, Amount);
4️⃣ Index Seek vs Index Scan
| Index Seek | Index Scan |
|---|---|
| Fast | Slower |
| Uses WHERE efficiently | Reads many rows |
| Preferred | Avoid if possible |
📌 Goal: Index Seek
5️⃣ Choosing the Right Columns for Index
Best Candidates
✔ Columns in WHERE clause
✔ JOIN columns
✔ ORDER BY columns
Avoid Indexing
❌ Low-cardinality columns (Gender, Status)
❌ Frequently updated columns
6️⃣ Index Column Order Matters
❌ Wrong Order
ON Orders(OrderDate, UserId);
✅ Correct Order
ON Orders(UserId, OrderDate);
📌 Equality columns first, range columns last.
7️⃣ Indexes and Stored Procedures
Common Mistake
Creating indexes without checking stored procedures.
Best Practice
- Analyze execution plan
- Identify scans
- Create index based on real queries
8️⃣ Missing Index Recommendation (Use Carefully)
Execution plan may show:
Missing Index
⚠️ Warning
- Suggestions are not always optimal
- Review before creating
9️⃣ Over-Indexing Problem
❌ Issues
- Slow INSERT/UPDATE/DELETE
- Increased storage
- Maintenance overhead
✅ Rule of Thumb
✔ Index for reads
✔ Monitor write performance
🔟 Maintaining Indexes
Fragmentation Problems
- Slower performance
Maintenance Commands
ALTER INDEX ALL ON Orders REBUILD;
or
ALTER INDEX ALL ON Orders REORGANIZE;
📌 Real Example: Slow Stored Procedure → Fast
❌ Slow Procedure
SELECT * FROM Orders WHERE UserId = @UserId;
❌ Execution Plan
- Table Scan
✅ Index Added
CREATE INDEX IX_Orders_UserId
ON Orders(UserId);
✅ Result
- Index Seek
- Faster execution
✅ Indexing Best Practices
✔ Use execution plans
✔ Avoid SELECT *
✔ Use INCLUDE columns
✔ Monitor index usage
✔ Remove unused indexes
📌 Index-Friendly Stored Procedure Template
CREATE PROCEDURE dbo.GetOrdersByUser
@UserId INT
AS
BEGIN
SET NOCOUNT ON;
SELECT OrderId, OrderDate, Amount
FROM Orders
WHERE UserId = @UserId;
END
✍️ Conclusion
Indexes are powerful tools, not magic buttons. Proper indexing can make stored procedures 10x faster, while poor indexing can slow everything down.
Understanding when, where, and how to use indexes is a key skill for every SQL Server developer.