SQL Server Index Types: A Quick Reference [Formatting Test]

2026-05-16 · 2 min read

sql-serverindexesdatabase

Note: This post exists to test blog prose formatting — tables, images, figcaptions, and code blocks. It is not intended as published content.

A quick reference for the index types I reach for most often in SQL Server, with notes on storage behavior and typical use cases.

Clustered vs Non-Clustered

The clustered index is the table — rows are physically sorted by the key. Every table should have one, and the key choice matters more than most developers realize.

-- Clustered: physical row order
CREATE CLUSTERED INDEX IX_Orders_OrderDate
ON dbo.Orders (OrderDate DESC);

-- Non-clustered: separate structure with row locator
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON dbo.Orders (CustomerID)
INCLUDE (OrderDate, TotalAmount);

The INCLUDE clause is one of the most underused features in SQL Server. Covering an index for a specific query avoids a key lookup entirely.

Index Type Comparison

Index Type	Storage	Best For	Key Limit
Clustered	Row data	Primary lookup, range scans	1 per table
Non-Clustered	Separate B-tree	Secondary lookups, covering	999 per table
Columnstore	Column segments	Aggregations, analytics	—
Filtered	Partial rows	Sparse data, known subsets	—

Columnstore for Analytics

If you’re running aggregations over millions of rows, columnstore is the right tool. Compression is excellent and batch mode execution changes the performance profile dramatically.

CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Sales_Columnstore
ON dbo.Sales (Year, Region, ProductID, Revenue);

A figcaption below an image — DM Mono, stone, centered.

Filtered Indexes

For columns with a highly skewed distribution — like a Status column where 95% of rows are 'Closed' — a filtered index on just the active rows keeps the index small and fast.

CREATE NONCLUSTERED INDEX IX_Tickets_Open
ON dbo.Tickets (AssignedTo, CreatedAt)
WHERE Status = 'Open';

The predicate must be simple (no functions, no OR across columns) but the wins on the right workload are significant.