Covering indexes in SQL Server: keys, INCLUDE, and lookup costs

An execution plan can show an Index Seek and still perform badly. A seek may identify thousands of candidate rows, each followed by a lookup into the clustered index to retrieve missing columns. The issue is not whether a seek exists, but how many additional operations the full request requires.

A covering index contains the columns needed by a particular query. Coverage is a relationship between an index and a query, not a universal property of the table. Add one column to SELECT or change a predicate, and yesterday's covering index may no longer cover the request. Begin with the exact statement and its common parameter distributions.

Design the key around navigation

In the example, the customer is constrained by equality, the date provides a range, and the output is sorted newest first. CustomerId therefore leads the key, followed by OrderedAt and a unique tie breaker. Total and Status are returned but do not determine the required ordering, so they belong in INCLUDE.

CREATE TABLE #Sales
(
    SaleId bigint NOT NULL PRIMARY KEY,
    CustomerId int NOT NULL,
    OrderedAt datetime2(0) NOT NULL,
    Total decimal(12,2) NOT NULL,
    Status tinyint NOT NULL
);
INSERT #Sales VALUES
(1,7,'2021-10-01',90,1),(2,7,'2021-10-02',40,0),
(3,8,'2021-10-02',20,1),(4,7,'2021-10-03',70,1);

CREATE INDEX IX_Sales_Customer_Date
ON #Sales(CustomerId, OrderedAt DESC, SaleId DESC)
INCLUDE(Total, Status);

DECLARE @CustomerId int=7;
DECLARE @From datetime2(0)='2021-10-01';
SELECT TOP (2) SaleId, OrderedAt, Total, Status
FROM #Sales
WHERE CustomerId=@CustomerId AND OrderedAt>=@From
ORDER BY OrderedAt DESC, SaleId DESC;

SET STATISTICS IO, TIME ON;
SELECT SaleId, OrderedAt, Total, Status
FROM #Sales
WHERE CustomerId=@CustomerId AND OrderedAt>=@From;
SET STATISTICS IO, TIME OFF;
DROP TABLE #Sales;

For customer 7, the first query returns sales 4 and 2. The index can support both the customer restriction and the requested ordering. If OrderedAt led the index instead, SQL Server could navigate the date range but might read many other customers before filtering them. This is why putting the globally most selective column first is an incomplete design rule.

A range condition commonly limits how effectively later key columns narrow a seek. Those later columns can still help ordering or coverage, but they are not automatically equivalent to earlier equality keys. Read the actual seek predicates and residual predicates. Measure rows read against rows returned rather than reasoning only from column names.

Make INCLUDE solve a measured problem

Included columns are stored at the nonclustered index leaf level and do not form its navigation order. They can remove lookups without expanding the logical search key. They still widen leaf pages, consume buffer-pool space, increase backup volume, and require maintenance when their values change. Including a frequently modified status column can turn a narrow read optimization into additional write work.

Do not automatically include every column mentioned in a missing-index recommendation. Such suggestions reflect a limited view of a query's estimated cost and do not merge overlapping workload requirements. Compare the proposal with existing indexes and consider whether one modest extension can serve multiple important statements. Preserve differences in key order when those differences support distinct access patterns.

A lookup is not inherently a defect. Ten lookups for ten rows can be cheaper than maintaining a wide index used only occasionally. The tipping point depends on row counts, caching, row width, and alternatives available to the optimizer. If one customer has ten rows and another has a million, both parameter cases belong in the evaluation.

Test the whole access pattern

The small temporary-table sample establishes the desired results. To evaluate performance, use a representative copy of the data and collect STATISTICS IO, elapsed time, CPU, and actual plans. Run selective and broad customer/date combinations. Avoid clearing production caches to manufacture a cold test; compare like-for-like runs and document cache conditions.

Also test writes: inserts, amount adjustments, status updates, and the retention process. If the index eliminates a sort, inspect memory grants and spills before and after. If a large query still chooses a scan, determine whether the scan is the sensible choice for its output volume. Forcing a narrow seek with hundreds of thousands of lookups can be worse.

The deployment decision should identify which statements benefit, which existing index may be redundant, and the added storage and write cost. Keep a script for the previous index definition and assess a normal business cycle. A useful covering index reduces total workload cost; it does not merely produce a more attractive plan icon.

Technical references: Microsoft Learn: Included columns · Microsoft Learn: Index design.