Nonclustered Index Design: Key Order, INCLUDE, Covering Indexes and Key Lookups

Version note: The T-SQL (Transact-SQL, Microsoft's dialect of SQL (Structured Query Language)) examples in this post were run on SQL Server 2025 CU10 (17.0.5035.5, compatibility level 170), and the C# examples with .NET 10, Entity Framework Core (EF Core) 10.0.14 and Microsoft.Data.SqlClient 7.1.1.

Guess First

Bookstore's customer panel has a "my orders" screen: the customer, a status and a start date are selected, and the list is sorted newest first. The query filters on exactly these three columns, so we create an index on exactly these three columns:

CREATE INDEX IX_Orders_Customer_Status_Created ON dbo.Orders (CustomerId, Status, CreatedAt);

EXEC sp_executesql N'SELECT OrderId, OrderNumber, Status, CreatedAt, TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId AND Status = @Status AND CreatedAt >= @From
    ORDER BY CreatedAt DESC',
    N'@CustomerId int, @Status varchar(20), @From datetime2(0)',
    @CustomerId = 2, @Status = 'Delivered', @From = '2026-01-01';

The query returns 3,163 rows. Will SQL Server use the new index? If it does, how many logical reads will it cost? Make your guess:

Table 'Orders'. Scan count 11, logical reads 7707, physical reads 0
  |--Parallelism(Gather Streams, ORDER BY:([Orders].[CreatedAt] DESC))
       |--Sort(ORDER BY:([Orders].[CreatedAt] DESC))
            |--Clustered Index Scan(OBJECT:([Orders].[PK_Orders]), WHERE:(...))

It did not. The index is on exactly the query's columns, yet the optimizer chose to scan the whole table. And it was right: when I forced the index with WITH (INDEX(...)), the query cost 9,712 logical reads, more than the scan. This post explains why and how to build the right index step by step: seeks and scans, key lookups and the tipping point, key column order, covering indexes with INCLUDE, filtered indexes, computed columns, their EF Core equivalents and the price every index costs on the write side.

The Scenario: An Order Search Endpoint

The starting point is the indexes SCHEMA.sql creates, which EF Core conventions would create too: the primary key (PK_Orders, clustered), UX_Orders_OrderNumber and IX_Orders_CustomerId for the foreign key. Before adding an index, I ran the query and captured the plan with SET STATISTICS PROFILE ON. The numbers on the left of the plan lines are the actual row count, the execution count and the estimated row count:

    Rows Executes EstimateRows  StmtText
    3163        1     8121.229    |--Parallelism(Gather Streams, ORDER BY:([Orders].[CreatedAt] DESC))
    3163       10     8121.229         |--Sort(ORDER BY:([Orders].[CreatedAt] DESC))
    3163       10     8121.229              |--Clustered Index Scan(OBJECT:([Orders].[PK_Orders]),
                                                 WHERE:([Orders].[CustomerId]=[@CustomerId] AND [Orders].[CreatedAt]>=[@From] AND [Orders].[Status]=[@Status]))

Notice one thing: the optimizer expects 8,121 rows and 3,163 actually come back. Customer 2 has 17,359 orders, and the optimizer estimates by treating the selectivity of the three conditions as partially independent. We will open up where this estimate comes from in post four, through statistics; for now remember this: index choice is based on the estimate, not the actual row count.

Why was the existing IX_Orders_CustomerId not used? Let's force it and see:

Table 'Orders'. Scan count 3, logical reads 53206, physical reads 0
   17359     10    17359.0   |--Index Seek(OBJECT:([Orders].[IX_Orders_CustomerId]), SEEK:([Orders].[CustomerId]=[@CustomerId]) ORDERED FORWARD)
    3163  17359 0.46783966   |--Clustered Index Seek(OBJECT:([Orders].[PK_Orders]), SEEK:([Orders].[OrderId]=[Orders].[OrderId]),
                                  WHERE:([Orders].[CreatedAt]>=[@From] AND [Orders].[Status]=[@Status]) LOOKUP ORDERED FORWARD)

The index seek found the customer's 17,359 rows. But the index only holds CustomerId and the clustering key OrderId; for Status, CreatedAt, OrderNumber and TotalAmount, every row requires a trip to the clustered index. That is what the word LOOKUP in the plan means: a key lookup. The Executes column says 17,359; the lookup ran separately for every row, and each one means descending the B-tree from root to leaf. 53,206 logical reads in total, seven times the scan.

Key Lookups and the Tipping Point

A key lookup costs about three page reads per row (the clustered index of Orders has three levels), and those reads are random. A scan reads 7,601 pages in order. So above a certain row count, a nonclustered index plus key lookups becomes more expensive than a scan. This is called the tipping point. It is not a fixed percentage; it depends on the table's page count, row width and the estimate. To find it in our data, I ran a SELECT * query for customers with different order counts, recompiling each time:

SELECT * FROM dbo.Orders WHERE CustomerId = 6 OPTION (RECOMPILE);

The switch happens between roughly 2,000 and 2,500 rows, only 0.2–0.25% of a million-row table. It is easy to think intuitively "as long as I'm not reading half the table, the index will be used"; in reality an index with lookups is abandoned at a very small fraction. The opening query fell back to a scan for the same reason: an estimate of 8,121 rows is far above this threshold. The fix is not to force the index but to eliminate the lookup.

Key Order: Equality First, Then Range

In a composite index, column order determines how narrow a range the seek can land on. The B-tree sorts rows by the first column, and by the second when the first is equal. If the columns searched by equality come first and the column searched by range comes last, the seek lands directly at the start of the wanted rows. If the range column moves ahead, the columns after it cannot take part in the seek; they become only a filter applied to the rows read (a residual predicate). To see the difference, I created two covering indexes and forced the same query for customer 1 (200,000 orders) and the Shipped status:

CREATE INDEX IX_A ON dbo.Orders (CustomerId, Status, CreatedAt) INCLUDE (OrderNumber, TotalAmount);
CREATE INDEX IX_B ON dbo.Orders (CustomerId, CreatedAt, Status) INCLUDE (OrderNumber, TotalAmount);
/* @CustomerId = 1, @Status = 'Shipped', @From = '2025-01-01' */

IX_A: Table 'Orders'. Scan count 1, logical reads 24
    2969  |--Index Seek(OBJECT:([Orders].[IX_A]),
               SEEK:([Orders].[CustomerId]=[@CustomerId] AND [Orders].[Status]=[@Status] AND [Orders].[CreatedAt] >= [@From]) ORDERED BACKWARD)
IX_B: Table 'Orders'. Scan count 1, logical reads 650
    2969  |--Index Seek(OBJECT:([Orders].[IX_B]),
               SEEK:([Orders].[CustomerId]=[@CustomerId] AND ([Orders].[CreatedAt], [Orders].[Status]) >= ([@From], [@Status])),
               WHERE:([Orders].[Status]=[@Status]) ORDERED BACKWARD)

Both are an "Index Seek", both return 2,969 rows, but one reads 24 pages and the other 650. In IX_B, the seek could only narrow down to the customer and the date range; it read every order customer 1 placed since 2025 and eliminated Status one row at a time with WHERE. Seeing "Seek" in a plan is not enough; read the SEEK: and WHERE: parts separately. The order among the equality columns themselves is less critical: look at which queries use which columns together. For example, if every query supplies CustomerId, putting it first makes the index usable for "the customer's orders" queries as well.

INCLUDE and Covering Indexes

The way to remove the lookup is to put every column the query needs into the index. Columns that need not be in the key are added only to the leaf level with INCLUDE; they take no part in sorting and do not grow the upper levels:

CREATE INDEX IX_Orders_Customer_Status_Created ON dbo.Orders (CustomerId, Status, CreatedAt)
    INCLUDE (OrderNumber, TotalAmount);

Table 'Orders'. Scan count 1, logical reads 25, physical reads 0
    3163     1   8121.229  |--Index Seek(OBJECT:([Orders].[IX_Orders_Customer_Status_Created]),
                                SEEK:([Orders].[CustomerId]=[@CustomerId] AND [Orders].[Status]=[@Status] AND [Orders].[CreatedAt] >= [@From]) ORDERED BACKWARD)

From 7,707 to 25. A single operator is left in the plan: no Sort, because the index is already in CreatedAt order and is read in reverse with ORDERED BACKWARD; no parallelism either. We never wrote OrderId into the index, yet we can return it, because the clustering key is already in every nonclustered index. The estimate is still 8,121, but it no longer matters: with this index, whatever the estimate, this is the cheapest path.

I measured the price too: this covering index is 6,860 pages, as big as the table itself (7,601 pages). With every extra column, a covering index turns into a second copy of the table. Keep the INCLUDE list to what genuinely frequent queries need, not "whatever the screen shows"; for a rarely run report, putting up with a lookup is often cheaper.

Filtered Indexes, Computed Columns and EF Core

The operations team's "pending orders" screen looks only at Pending orders, and there are 4,800 of them among a million rows. Instead of indexing the whole table, we can index only those rows:

CREATE INDEX IX_Orders_Pending ON dbo.Orders (CreatedAt)
    INCLUDE (CustomerId, TotalAmount)
    WHERE Status = 'Pending';

The index is just 21 pages. But for a filtered index to be usable, the optimizer must be able to prove at compile time that the query's condition is covered by the index filter. EF Core can translate the same LINQ (Language Integrated Query) in two different ways: a constant in the code is embedded in the SQL as a literal, a variable becomes a parameter. I tried this with two models, one where the column is marked IsUnicode(false) and one where it is not:

var pending1 = await db.Orders.Where(o => o.Status == "Pending")
    .OrderBy(o => o.CreatedAt)
    .Select(o => new { o.OrderId, o.CustomerId, o.CreatedAt, o.TotalAmount })
    .ToListAsync();

string status = "Pending";
var pending2 = await db.Orders.Where(o => o.Status == status)
    .OrderBy(o => o.CreatedAt)
    .Select(o => new { o.OrderId, o.CustomerId, o.CreatedAt, o.TotalAmount })
    .ToListAsync();

IsUnicode(false) on Status: False
  WHERE [o].[Status] = N'Pending'
  constant: 4800 rows, logical reads 7707
  WHERE [o].[Status] = @status
  variable: 4800 rows, logical reads 7707
IsUnicode(false) on Status: True
  WHERE [o].[Status] = 'Pending'
  constant: 4800 rows, logical reads 20
  WHERE [o].[Status] = @status
  variable: 4800 rows, logical reads 7707

Only one of the four attempts used the index. A parameterized query can never use it, because the cached plan must also be correct for any other value of @status. With a constant literal, the type matters: when the model thinks the column is Unicode, EF Core generates N'Pending', SQL Server has to convert the varchar column to nvarchar, and the filter no longer matches. Aligning the model with the column's real type (IsUnicode(false)) is the first step both here and with the implicit conversion problems we will see in post six. If you want a filtered index used by parameterized queries, you have to write the filter into the query as a constant.

An index on a computed column relies on a similar matching. A condition that wraps the column in a function, like YEAR(CreatedAt) = 2024, cannot seek on an index and scans. When I added the same expression as a computed column and indexed it, the plan changed without touching the query:

ALTER TABLE dbo.Orders ADD CreatedYear AS YEAR(CreatedAt);
CREATE INDEX IX_Orders_CreatedYear ON dbo.Orders (CreatedYear);

SELECT COUNT(*) FROM dbo.Orders WHERE YEAR(CreatedAt) = 2024;  /* the query is unchanged */

263520
Table 'Orders'. Scan count 11, logical reads 7720   /* before */
263520
Table 'Orders'. Scan count 1, logical reads 464     /* after */

The optimizer matched the YEAR(CreatedAt) expression in the query to the computed column's definition. This requires the expression to be deterministic and the session's SET options, such as QUOTED_IDENTIFIER and ANSI_NULLS, to be right. The cleaner fix is often to write the query as a range (CreatedAt >= '2024-01-01' AND CreatedAt < '2025-01-01'); the computed column is for cases where you cannot change the query.

A unique index also gives the optimizer information: thanks to UX_Orders_OrderNumber, an equality on OrderNumber is known to return at most one row, and joins are planned accordingly. In EF Core all of these indexes are defined on the model and migrations generate them. The output I got from GenerateCreateScript():

e.HasIndex(o => new { o.CustomerId, o.Status, o.CreatedAt })
    .HasDatabaseName("IX_Orders_Customer_Status_Created")
    .IncludeProperties(o => new { o.OrderNumber, o.TotalAmount });

e.HasIndex(o => o.CreatedAt)
    .HasDatabaseName("IX_Orders_Pending")
    .IncludeProperties(o => new { o.CustomerId, o.TotalAmount })
    .HasFilter("[Status] = 'Pending'");

e.HasIndex(o => o.OrderNumber).IsUnique();

CREATE INDEX [IX_Orders_Customer_Status_Created] ON [Orders] ([CustomerId], [Status], [CreatedAt]) INCLUDE ([OrderNumber], [TotalAmount]);
CREATE UNIQUE INDEX [IX_Orders_OrderNumber] ON [Orders] ([OrderNumber]);
CREATE INDEX [IX_Orders_Pending] ON [Orders] ([CreatedAt]) INCLUDE ([CustomerId], [TotalAmount]) WHERE [Status] = 'Pending';

The text you pass to HasFilter goes into the SQL as is; EF Core does not validate it. Use the experiment above to check that the type of the literal in the filter matches the literal your queries generate.

Write Cost and Missing Index Suggestions

Every nonclustered index is updated separately on every INSERT and DELETE on the table, and on every UPDATE that touches its columns. I inserted 1,000 new orders in a transaction, measured the log they produced, then rolled back:

BEGIN TRANSACTION;
INSERT dbo.Orders (OrderNumber, CustomerId, Status, CreatedAt, TotalAmount, Currency)
SELECT CONCAT('NEW-', value), 1 + (value * 7919) % 100000, 'Pending',
       DATEADD(SECOND, value, '2026-10-21'), 10, 'EUR'
FROM GENERATE_SERIES(1, 1000);

SELECT dt.database_transaction_log_record_count AS LogRecords,
       dt.database_transaction_log_bytes_used AS LogBytes
FROM sys.dm_tran_database_transactions AS dt
JOIN sys.dm_tran_current_transaction AS ct ON ct.transaction_id = dt.transaction_id
WHERE dt.database_id = DB_ID();
ROLLBACK;

Config              Indexes LogRecords LogBytes
------------------- ------- ---------- --------
EF Core conventions 3       1367       3455588
+ 3 indexes         6       4170       5226760

Three more indexes (the covering one, the filtered one and a plain index on CreatedAt) tripled the log records and increased the log bytes by 51%. You pay this difference again on every insert, every deploy, every index maintenance run and every log byte shipped to replicas. Measure the cost on the write side just as you measure the gain on the read side.

So which indexes should we create? While optimizing, SQL Server collects the indexes it considers missing in the sys.dm_db_missing_index_* DMVs (Dynamic Management Views). The suggestions after running two queries without the indexes:

equality_columns       inequality_columns included_columns                         user_seeks avg_user_impact
---------------------- ------------------ ---------------------------------------- ---------- ---------------
[CustomerId], [Status] [CreatedAt]        NULL                                     1          97.51
[Status]               NULL               [OrderNumber], [CreatedAt], [TotalAmount] 1          80.60

The first suggestion found the key we arrived at but suggested no INCLUDE; the second included CreatedAt instead of putting it in the key for sorting, and did not suggest a filtered index. Microsoft's documentation states the limits plainly: suggestions are based on the estimates of a single query's optimization, do not specify key column order, never propose unique or filtered indexes, do not weigh the size of included columns, and at most 600 groups are collected. Take a suggestion as a hint, merge it with existing indexes, then measure.

How SQL Server 2019/2022 differ: index structure, key lookups, filtered indexes and computed column matching are the same in all three versions. The option (PLAN PER VALUE(... QueryVariantID ...)) suffixes visible in this post's plans come from PSP (Parameter Sensitive Plan) optimization, introduced with compatibility level 160; on 2019 (level 150) the same query runs with a single plan. We will cover PSP in post seven.

In Production

Design indexes for your most frequently run queries. Find the top ten queries by total logical reads in Query Store or sys.dm_exec_query_stats; three or four well-designed indexes often solve most of the workload.

Clean up unused indexes. sys.dm_db_index_usage_stats keeps seek, scan, lookup and update counts for every index; an index that is never read but constantly updated is pure cost. Remember the counters reset on server restart, so observe over a long enough period.

Think about locks when creating an index in production. On Enterprise, ONLINE = ON keeps the table available; on Standard, index creation blocks writes for its duration. Plan it into a maintenance window for large tables.

Common Pitfalls

Creating a separate index on every column. Three separate indexes on CustomerId, Status and CreatedAt do not replace one composite index; the optimizer rarely finds combining them worthwhile, and you pay three times the write cost.

Applying missing index suggestions as they are. Five very similar suggestions can arrive for the same table; creating all five means five times the write cost. Merge suggestions and decide the key order yourself.

Relaxing when you see "Index Seek" in a plan. In the IX_B example, the seek read 650 pages. If there is a residual predicate in the WHERE: part, know that the seek is actually scanning a wide range.

A mismatch between the model and the column type. If you do not say IsUnicode(false) in EF Core for a varchar column, filtered indexes will not match and, as we will see in post six, with some collations a seek turns into a full scan.

A Small Experiment

★☆☆ With the IX_Orders_Customer_Status_Created covering index in place, add the Currency column to the query. How do the plan and the logical reads change? Did the key lookup come back?

★★☆ Repeat the tipping point table with a query that selects only OrderId, CreatedAt instead of SELECT *. Does the threshold change? Why?

★★★ Design an index on OrderItems for the query "units sold of a book in the last 90 days" (BookId; the order date lives in Orders). First look for a single-table solution, then measure the write cost with a 1,000-row insert and justify your design.

The optimizer's estimates kept showing up in this post: 8,121 rows expected and 3,163 delivered, tipping point decisions made on estimates. In the next post we go to the source of those estimates: statistics, histograms, the density vector and where cardinality estimation goes wrong.