Multi-Column Index Key Order: Which Column Goes First

Swapping two index columns can change a seek into a scan. Index key order tells SQL Server which values it can locate first, so the WHERE clause deserves more attention than a slogan about selectivity. Test the actual predicate shape before choosing the first key.

How Index Key Order Builds Different Routes

A B-tree index sorts by the leading key, then by the next key within each leading value. An index on (Status, OrderDate) groups each status together and sorts dates inside that group. An index on (OrderDate, Status) groups by date first. Those are different routes through the same table. Neither is universally better. I have seen a team choose the most selective column first without checking whether the query uses equality or a range.

Use a small lab table so the plan is visible. The sample below creates two competing indexes. It deliberately keeps the selected value narrow, avoiding key lookups that would distract from the key-order lesson. Run it in a disposable session, not as a change to an application table.

CREATE TABLE #KeyOrderDemo
(
    OrderID int NOT NULL PRIMARY KEY CLUSTERED,
    StatusCode char(1) NOT NULL,
    OrderDate date NOT NULL
);
WITH n AS
(
    SELECT TOP (100000)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b
)
INSERT #KeyOrderDemo (OrderID, StatusCode, OrderDate)
SELECT CONVERT(int, rn),
       CASE WHEN rn % 10 = 0 THEN 'O' ELSE 'C' END,
       DATEADD(day, CONVERT(int, rn % 730), '20240101')
FROM n;
CREATE INDEX IX_KeyOrder_StatusDate
ON #KeyOrderDemo (StatusCode, OrderDate);
CREATE INDEX IX_KeyOrder_DateStatus
ON #KeyOrderDemo (OrderDate, StatusCode);

Equality Followed by a Date Range

When StatusCode has an equality condition and OrderDate has a range, the status-first index can seek to one status group, then to the date interval inside it. The date-first index can seek the date interval but can need to evaluate status across that interval. Compare the Seek Predicates in each actual plan. A predicate shown only under Predicate, outside Seek Predicates, is being evaluated after the access path has read candidates.

SET STATISTICS IO ON;
SELECT COUNT_BIG(*) AS matching_orders
FROM #KeyOrderDemo WITH (INDEX(IX_KeyOrder_StatusDate))
WHERE StatusCode = 'O'
  AND OrderDate >= '20250101'
  AND OrderDate < '20250201';
SELECT COUNT_BIG(*) AS matching_orders
FROM #KeyOrderDemo WITH (INDEX(IX_KeyOrder_DateStatus))
WHERE StatusCode = 'O'
  AND OrderDate >= '20250101'
  AND OrderDate < '20250201';
SET STATISTICS IO OFF;

The index hints are for this comparison only. They stop the optimizer from choosing the cheaper path on its own. Remove the hints in application code unless you have separate evidence for them. Record logical reads, not only elapsed time. Cache effects can make two tiny runs look identical even when one reads many more index pages.

Reverse the Predicate Shape

A date equality combined with a status range changes the useful leading key. In a real application, a report can ask for one day across several statuses, while another asks for one status across a month. Neither index covers both perfectly at every scale. The query below tests a single day and a status interval. Inspect which key appears as the first Seek Predicate and whether the second condition is also part of the seek.

SELECT COUNT_BIG(*) AS matching_orders
FROM #KeyOrderDemo
WHERE OrderDate = '20250115'
  AND StatusCode BETWEEN 'C' AND 'O'
OPTION (RECOMPILE);

I compare the result with an unhinted actual plan first. If the optimizer chooses an unexpected path, inspect estimates and statistics before declaring it confused. The data distribution in the real table matters. A rare status and a common date are different from a common status and a rare date. The first column of an index also gets the index statistics histogram, which affects estimates.

With equality on both keys, either permutation can seek to one narrow pair of values. The choice then depends on other queries, sort order, and how the distribution is represented in statistics. A range on the leading key is different. The second key can be checked while scanning that range. It does not always narrow the start and end points as much as a leading equality does. Read the graphical plan properties, not just the Index Seek label. Seek Predicates show what locates rows; Predicate shows what filters candidates afterward. That distinction is the reason the two indexes are worth comparing.

Do Not Pretend OR Is an AND

An OR predicate has a different shape. StatusCode = 'O' OR OrderDate >= '20250101' asks for either group. SQL Server can combine index inputs, scan a covering index, or choose another plan. Both indexes can be useful, yet neither makes the predicate a single contiguous two-key range. Test the OR case directly rather than extending a rule from the AND case.

SELECT COUNT_BIG(*) AS matching_orders
FROM #KeyOrderDemo
WHERE StatusCode = 'O'
   OR OrderDate >= '20250101'
OPTION (RECOMPILE);

An OR across different columns can also invite a rewrite as two branches with UNION ALL, but only after handling rows that match both branches. Otherwise the result changes. What did the plan actually read, and did the rewrite preserve the same rows? A clean plan icon is not a substitute for a correct result.

Choose Index Key Order for the Queries You Have

Start with equality predicates that appear together in important queries, then consider the range and ordering requirements. A leading equality can let the next key narrow a range. An inequality on the first key usually limits how much the next key helps seek within that range. Sort order, joins, included columns, and writes also matter. An index that serves a frequent report can slow an insert-heavy table if it duplicates another large index.

Do not create both permutations by default. Compare workload frequency and cost, then inspect existing indexes for overlap. If a query needs only columns already in the chosen index, it can avoid lookups. If it needs many other columns, key order alone will not rescue it. I keep the plan's Seek Predicates, residual Predicate, estimated rows, actual rows, and logical reads together so the choice can be reviewed later.

Recheck After Data Distribution Changes

As data shifts, status proportions and date density shift too. A status-first index chosen when open orders were rare can behave differently after the application changes its workflow. Refresh statistics as appropriate and retest representative values. Parameterized queries can reuse one plan across values that straddle different cost choices, so test more than one parameter set.

The durable rule is modest: equality on the leading key opens a useful route, a range can limit later keys, and OR needs its own test. Let measured queries decide which route deserves an index. A tidy diagram of the key list is helpful, but the actual plan is the receipt.

Related reading on this blog: Execution Plans and Indexing Strategies: Quick Guide and Optimize Key Lookup by Creating Index with Include Columns.

Index key order is not a guess, it is a choice tested against predicates and reads.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.