Why Index?

Indexes dramatically improve query performance by reducing the amount of data SQL Server must scan. A well‑designed index can turn a table scan into an index seek, saving CPU, I/O, and latency.

Common Index Types

Design Guidelines

1. Start with the workload. Identify the most frequent and costly queries.
2. Choose the right key order. Put columns used in WHERE clauses first, then columns for sorting.
3. Avoid over‑indexing. Each index adds write overhead.
4. Use INCLUDE columns. Add non‑key columns to cover queries without widening the key.
5. Consider filtered indexes. Narrow the index to the needed rows.
6. Monitor fragmentation. Rebuild or reorganize when fragmentation exceeds 30%.

Practical Examples

1. Covering Index for a Search Query

CREATE NONCLUSTERED INDEX IX_Orders_CustomerDate
ON dbo.Orders (CustomerID, OrderDate)
INCLUDE (TotalAmount, Status);

2. Filtered Index for Active Records

CREATE NONCLUSTERED INDEX IX_Products_Active
ON dbo.Products (CategoryID)
WHERE IsActive = 1;

3. Columnstore Index for Reporting

CREATE CLUSTERED COLUMNSTORE INDEX CX_Sales
ON dbo.SalesFact;

Monitoring & Maintenance

Use built‑in DMVs to assess index health:

SELECT 
    DB_NAME() AS DatabaseName,
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    s.avg_fragmentation_in_percent,
    s.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS s
JOIN sys.indexes AS i
    ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.page_count > 100
ORDER BY s.avg_fragmentation_in_percent DESC;

Further Reading

• Create Indexes (Microsoft Docs)
• Covering Indexes – SQLPerformance.com
• Filtered Indexes – Brent Ozar