SQL Server Indexes

This guide covers all the main SQL Server index types and when to use each one. An index in SQL Server is one of the most fundamental structures for fast data access, and it directly affects query performance. This post covers what an index actually is, which type to use when, a simple CREATE INDEX example, and how to keep indexes maintained over time.

What Is an Index in SQL Server?

An index is a data structure that stores the values of one or more columns in sorted order, along with a pointer back to the corresponding row in the table. Think of it like the index at the back of a book: without it, SQL Server has to scan the entire table (a table scan) to find what it’s looking for; with the right index in place, it can go straight to the matching rows.

SQL Server Index Types

• Clustered Index: Determines the physical order of the data in the table. Each table can have only one, and it’s typically created automatically on the primary key.
• Non-Clustered Index: Builds a separate lookup structure for the specified column(s) without changing the table’s physical order. A table can have multiple non-clustered indexes.
• Unique Index: Enforces that no duplicate values exist in the indexed column(s), guaranteeing data integrity.
• Filtered Index: Built on only a subset of rows that match a WHERE condition — for example, only rows where IsActive = 1. Smaller and faster than indexing the whole table.
• Columnstore Index: Stores data column-by-column instead of row-by-row, with heavy compression. Delivers dramatic performance gains for reporting and analytical (data warehouse) queries over large datasets.
• Full-Text Index: Enables word- and phrase-based search on text columns — much faster than a classic LIKE '%...%' scan.

A Simple Example: Creating an Index

If you frequently query an Orders table by CustomerId, a non-clustered index looks like this:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON dbo.Orders (CustomerId)
INCLUDE (OrderDate, TotalAmount);

The INCLUDE clause adds frequently-used columns to the index’s leaf level, so SQL Server can satisfy the query entirely from the index without a key lookup back to the table — this is called a covering index.

Index Maintenance: Tracking Fragmentation

Ongoing inserts, updates and deletes gradually fragment your indexes. This query lists the most fragmented indexes in a database:

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

As a rule of thumb: REORGANIZE when fragmentation is between 5-30%, and REBUILD above 30%.

When Should You Add an Index — and When Shouldn’t You?

• Add one on columns with high selectivity (few repeating values) that appear frequently in WHERE, JOIN, and ORDER BY clauses.
• Skip it on columns that are updated often but rarely queried, or when you already have several overlapping indexes — every extra index adds maintenance overhead to every INSERT/UPDATE.
• Audit regularly with the sys.dm_db_index_usage_stats DMV to see which indexes are actually being used and which are just consuming disk space.

The right indexes, on the right columns, can multiply query performance. Missing or excessive indexes do the opposite — which is why indexing strategy isn’t a one-time task, it’s something to revisit continuously.