A covering index contains every column a query needs — as key columns or as INCLUDE'd nonkey columns — so SQL Server can answer the query entirely from the index without going back to the base table.
Because it eliminates per-row key lookups, a covering index can cut a query's logical reads dramatically — in many cases from hundreds of reads down to a handful.
Covering indexes are one of the most effective single changes you can make to a read-heavy query. This guide explains the key-lookup problem they solve, how to choose key vs. included columns, and where the trade-offs are.
The problem: key lookups
A key lookup is the hidden tax on a query that an index almost covers. SQL Server uses a nonclustered index to find the matching rows, but if the query needs columns the index doesn't contain, it must fetch them from the clustered index — once per row. Per practitioner analysis, a query returning 1,000 rows can trigger 1,000 extra reads that way. A covering index removes that round trip entirely.
Key columns vs. included columns
A covering index has two kinds of columns, and using them correctly is the whole craft:
- Key columns
- Form the sorted b-tree of the index. Use them for the columns you seek, range-filter, join, and order on. Keep this set small — a narrow key is efficient.
- Included (nonkey) columns
- Stored only at the leaf level and not sorted. Use
INCLUDEfor columns the query only needs to return, so the index can cover the query without bloating the key.
Per Microsoft's guidance, included columns let you create nonclustered indexes that cover more queries while keeping the index key small and efficient.
An example
Cover this querySELECT OrderId, OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId;
-- CustomerId is searched on → key column.
-- OrderId, OrderDate, TotalAmount are only returned → INCLUDE.
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Covering
ON dbo.Orders (CustomerId)
INCLUDE (OrderId, OrderDate, TotalAmount);
With this index, the CustomerId predicate seeks, and every returned column is already present — so the key lookup disappears.
The payoff shows up directly in logical reads: covering a query commonly drops it from hundreds of reads to single digits. Confirm it with SET STATISTICS IO ON before and after.
The trade-offs
Indexes are not free. Every index must be maintained on every insert, update, and delete, and a wide covering index with many included columns costs more to store and slows writes more. The discipline:
- Cover the queries that matter, not every query — usually your most frequent or most expensive ones.
- Avoid redundant or overlapping indexes that duplicate coverage and tax writes for no read benefit.
- Aim for the smallest index that covers the important workload.
How automated analysis recommends them
A missing covering index is one of the clearest signals in an execution plan — a key lookup paired with a high read count. SprocOptimizer includes index changes among the recommendation types its analysis produces, and any index or query change it proposes is validated for identical results and measured before/after — so you adopt the indexes that genuinely help and skip the ones that only add write cost.
Frequently asked questions
A covering index is a nonclustered index that contains every column a query needs — as key columns or as INCLUDE'd nonkey columns — so SQL Server can answer the query entirely from the index without going back to the base table. Because it avoids extra lookups, a covering index can cut a query's logical reads dramatically.
Key columns form the sorted b-tree of the index and are used for seeking, range filtering, and ordering; they should be the columns you search and join on. Included (nonkey) columns are stored only at the leaf level and are not sorted — they exist so the index can return extra output columns without a lookup. Using INCLUDE keeps the index key small and efficient while still covering the query.
A key lookup happens when a nonclustered index finds the matching rows but still has to fetch additional columns from the clustered index, once per row. If the query returns many rows, that is many extra reads. When the index already contains every column the query needs, there is nothing left to fetch, so the key lookup disappears and reads drop sharply.
Yes. Every index adds storage and slows writes, because inserts, updates, and deletes must maintain it. A wide covering index with many included columns costs more to maintain and store. Cover the queries that matter rather than every query, and avoid redundant or overlapping indexes — the goal is the smallest index that covers the important workload.
Primary sources & further reading
- Redgate Simple Talk — Using Covering Indexes to Improve Query Performance.
- Microsoft Learn — Create Indexes with Included Columns.
- MSSQLTips — Covering Index in SQL Server with Key and Non-Key Columns.
The right index, validated before it ships
SprocOptimizer recommends covering and other index changes from a real workload and measures their before/after impact — on-premises, with no row-level data leaving your network.
Request a Demo Read the Optimization Guide