Indexing is the single biggest lever most teams have over query performance - and the one most often misused. Add the right index and a query that scanned millions of rows now touches a handful. Add the wrong ones and you quietly tax every write on the table while the slow query stays slow. This guide explains how SQL Server indexes actually work and how to choose them deliberately.

How B-tree indexes work

A SQL Server index is a balanced tree (a B-tree, more precisely a B+ tree). At the top is a single root page; below it are intermediate levels; at the bottom are the leaf pages that hold the indexed data in sorted order. To find a value, the engine starts at the root and follows pointers down through a few levels until it reaches the leaf - so even a table with tens of millions of rows is only a handful of page reads deep. That’s why an index seek is so much cheaper than scanning the whole table.

The catch is that this sorted structure has to be maintained. Every insert, update or delete that touches an indexed column has to keep the tree ordered and balanced, which is real work. That single fact explains almost every indexing trade-off: reads get faster, writes get slower.

Clustered versus non-clustered indexes

SQL Server has two fundamental index types, and understanding the difference is the foundation of everything else.

The clustered index is the table

A clustered index defines the physical order in which rows are stored - the leaf level of the clustered index is the table’s data. Because rows can only be laid out one way, a table can have exactly one clustered index. The best clustering key is usually narrow, unique and ever-increasing (an IDENTITY integer is the classic choice), because that keeps inserts appending to the end rather than forcing page splits in the middle.

Non-clustered indexes point back

A non-clustered index is a separate structure holding a sorted copy of its key columns plus a pointer - the clustered key - back to the full row. A table can have many of them. When a query filters on a non-clustered key but needs columns the index doesn’t contain, SQL Server performs a key lookup back into the clustered index for each row. A few lookups are fine; thousands can make the optimiser abandon the index and scan instead.

A clustered index decides how the table is stored. Every other index is a shortcut that still has to find its way back home. - The Solution On IT database team

Covering indexes and INCLUDE columns

The cure for expensive key lookups is a covering index - one that contains every column the query needs, so the result can be served from the index alone. You don’t want to bloat the index key with columns you never search on, so SQL Server lets you add them as non-key INCLUDE columns: they live only at the leaf level, adding the data without widening the tree that gets searched.

Here’s a covering index for a common query that filters recent orders by customer and returns their date and total:

sql
-- Query we want to make fast:
-- SELECT OrderDate, TotalAmount FROM dbo.SalesOrder
-- WHERE CustomerId = @id AND OrderDate >= @from;

CREATE NONCLUSTERED INDEX IX_SalesOrder_Customer_Date
    ON dbo.SalesOrder (CustomerId, OrderDate)
    INCLUDE (TotalAmount)
    WITH (FILLFACTOR = 90, ONLINE = ON);

The key columns CustomerId, OrderDate let SQL Server seek straight to the right customer and date range; TotalAmount rides along in INCLUDE so the query never has to touch the base table. That’s a covering index - often the difference between milliseconds and seconds.

Getting composite column order right

In a multi-column (composite) index, column order is everything. The index is sorted by the first key column, then by the second within the first, and so on - like a phone book sorted by surname then first name. Two rules follow directly:

  • Equality before range. Put columns used with = before columns used with a range (>, <, BETWEEN). In the index above, CustomerId (equality) correctly comes before OrderDate (range). Reverse them and the seek can’t narrow efficiently.
  • Selectivity matters. A more selective column - one that filters down to fewer rows - generally earns its place as a leading key, because it does the most to shrink the search early.

An index on (OrderDate, CustomerId) looks similar but serves a completely different set of queries than (CustomerId, OrderDate). Design the order around the predicates your queries actually use.

Fill factor, fragmentation and maintenance

Fill factor controls how full each leaf page is packed when an index is built or rebuilt. Leaving a little free space (a fill factor of, say, 90) gives room for future inserts and updates, reducing costly page splits - but too low a fill factor wastes space and I/O. For ever-increasing keys you can pack pages full; for keys inserted in the middle, leave headroom.

Over time, inserts and deletes cause fragmentation - pages become out of order and partly empty, so the same data takes more reads. Maintenance addresses it:

  • REORGANIZE is a lighter, always-online operation that defragments the leaf level in place - a good fit for light-to-moderate fragmentation.
  • REBUILD recreates the index from scratch, fully removing fragmentation and refreshing statistics - better for heavy fragmentation, at a higher cost.
  • UPDATE STATISTICS keeps the optimiser’s picture of data distribution current so it keeps choosing good plans. A rebuild refreshes statistics automatically; a reorganize does not.

Missing-index DMVs and filtered indexes

SQL Server records indexes it thinks would have helped, exposed through the missing-index dynamic management views (such as sys.dm_db_missing_index_details). Treat these strictly as hints, not orders. They’re generated per query in isolation, ignore the write cost, and frequently suggest overlapping or redundant indexes. Read them as evidence of a workload pattern, then design one good index by hand rather than blindly creating everything they propose.

A filtered index covers only a subset of rows via a WHERE clause - for example, indexing only Status = 'Pending' when the vast majority of orders are completed. It’s smaller, cheaper to maintain and highly effective when your queries consistently target that same slice of data.

When not to index

  • Write-heavy tables. On a table dominated by inserts and updates, every extra index is a tax on every write. Keep indexing lean where throughput matters most.
  • Low-selectivity columns. Indexing a column with only a few distinct values (like a boolean or a two-option status) rarely helps - the optimiser will usually scan anyway.
  • Redundant and overlapping indexes. An index on (A) is often already covered by one on (A, B). Duplicates cost writes and storage for no read benefit.
  • Tiny tables. For a table of a few hundred rows, a scan is already fast; an index adds maintenance for no measurable gain.

The cost of too many indexes is real and cumulative: slower inserts, updates and deletes; larger backups; longer maintenance windows; and more chances for the optimiser to pick a poor plan. Every index should earn its place.

Best practices to follow

  1. Choose a narrow, ever-increasing clustered key. It keeps every non-clustered index smaller and inserts cheap.
  2. Design covering indexes for your hottest queries. Use INCLUDE to eliminate key lookups without bloating the key.
  3. Order composite keys equality-first, then by selectivity. Match the order to your real predicates.
  4. Maintain indexes on a schedule. Reorganize or rebuild based on fragmentation, and keep statistics current.
  5. Verify with usage stats, then prune. Drop indexes that are never sought and consolidate overlapping ones.

Indexing is one part of a larger performance picture - pair it with plan analysis and SARGable queries, covered in our query performance tuning guide. If you’d rather hand it to a specialist, our SQL Server development service designs and tunes indexing strategies against your real workload.

Frequently asked questions