Composite Index Without the Leading Key: Seek, Scan and Residual Filters

A composite index orders its keys together, so a filter on the second key needs careful plan reading. The useful question is how much work returns the complete answer.

Read the composite index in key order

Consider an index on Region and OrderDate. Rows are grouped by Region, then ordered by date within each group. July dates belong to separate regional ranges. A date-only request does not supply a particular leading region.

Calling the second key useless would go too far. It still orders dates within each region and can participate in filtering. This covering index also contains the returned columns. I inspect the actual access path before recommending another index.

Key order is the design principle behind this test. It does not guarantee a particular physical plan. Optimizer choices depend on the schema, data, available indexes and SQL Server environment.

Keep the comparison controlled

The demo table holds 50,000 orders across NE and SW, with dates spread through 2025. A clustered primary key stores OrderId. Each row also contains a deliberately wide payload. The RegionDate nonclustered index includes Amount and covers our four returned fields.

Run this setup first in a new query window. It builds the sample rows as a temporary table. The queries that follow use it one after another, so keep the same window open.

DROP TABLE IF EXISTS #Orders;
CREATE TABLE #Orders
(OrderId int NOT NULL PRIMARY KEY CLUSTERED,
 Region char(2) NULL, OrderDate date NOT NULL,
 Amount decimal(12,2) NOT NULL, Payload char(300) NOT NULL);
;WITH Digit(n) AS
(SELECT n FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) d(n)),
Numbers(n) AS
(SELECT 1+a.n+10*b.n+100*c.n+1000*d.n+10000*e.n
 FROM Digit a CROSS JOIN Digit b CROSS JOIN Digit c
 CROSS JOIN Digit d CROSS JOIN Digit e)
INSERT #Orders(OrderId,Region,OrderDate,Amount,Payload)
SELECT n,CASE WHEN n%2=0 THEN 'NE' ELSE 'SW' END,
 DATEADD(day,n%365,CONVERT(date,'20250101',112)),
 CONVERT(decimal(12,2),n%100+1),REPLICATE('x',300)
FROM Numbers WHERE n<=50000;

Now the first phase. I turn on STATISTICS IO so the logical reads show up in the Messages tab.

CREATE INDEX IX_RegionDate
 ON #Orders(Region,OrderDate) INCLUDE(Amount);
SET STATISTICS IO ON;
SELECT OrderId,Region,OrderDate,Amount FROM #Orders
WHERE OrderDate>='20250701' AND OrderDate<'20250801';

The first three phases use only the RegionDate nonclustered index. The fourth drops it before creating the date-first alternative. This avoids comparing two competing definitions accidentally. No index hint forces a preferred operator.

Read access predicates and rows read

An Index Seek uses its seek predicates to locate qualifying ranges. Its separate Predicate can filter rows after that access. An Index Scan can also carry a Predicate.

Inspect the actual index name, predicates and row counts together. Compare rows read with rows returned, including repeated operator executions. Keep the complete STATISTICS IO messages beside the actual plans. A seek label alone does not establish less work.

Supply leading values only when complete

The demo table starts with exactly two nonnull regions. Listing both supplies the leading values explicitly. That preserves the date-only request for this limited domain. A fixed list becomes incorrect when the domain changes.

SELECT OrderId,Region,OrderDate,Amount FROM #Orders
WHERE Region IN('NE','SW')
 AND OrderDate>='20250701' AND OrderDate<'20250801';

A distinct-region join discovers values from the same data. That discovery has its own work. It also uses ordinary equality, which does not match NULL to NULL. Query syntax alone does not promise separate seeks.

;WITH Regions AS(SELECT DISTINCT Region FROM #Orders)
SELECT o.OrderId,o.Region,o.OrderDate,o.Amount
FROM Regions r JOIN #Orders o ON o.Region=r.Region
WHERE o.OrderDate>='20250701' AND o.OrderDate<'20250801';

Test a new region and NULL

Now add an AP order and a NULL-region order, both dated July 15, and count what each request returns. The date-only filter includes both. The static NE/SW list excludes both. The discovered-region equality join includes AP but loses the NULL order. The second query lists the rows the static list misses.

INSERT #Orders VALUES
 (50001,'AP','20250715',12.00,REPLICATE('x',300)),
 (50002,NULL,'20250715',13.00,REPLICATE('x',300));

SELECT 'Date only' AS Request, COUNT(*) AS ReturnedRows FROM #Orders
WHERE OrderDate>='20250701' AND OrderDate<'20250801'
UNION ALL
SELECT 'Static NE/SW list', COUNT(*) FROM #Orders
WHERE Region IN('NE','SW') AND OrderDate>='20250701' AND OrderDate<'20250801'
UNION ALL
SELECT 'Discovered regions', COUNT(*)
FROM (SELECT DISTINCT Region FROM #Orders) r
JOIN #Orders o ON o.Region=r.Region
WHERE o.OrderDate>='20250701' AND o.OrderDate<'20250801';

SELECT OrderId,Region,OrderDate,Amount FROM #Orders
WHERE OrderDate>='20250701' AND OrderDate<'20250801'
EXCEPT
SELECT OrderId,Region,OrderDate,Amount FROM #Orders
WHERE Region IN('NE','SW') AND OrderDate>='20250701' AND OrderDate<'20250801';

A separate NULL branch repairs the equality join’s logical omission. UNION ALL preserves that branch without duplicate removal. The nonnull and NULL branches are disjoint. This is a correctness example, rather than a promised performance improvement.

;WITH Regions AS
(SELECT DISTINCT Region FROM #Orders WHERE Region IS NOT NULL)
SELECT o.OrderId,o.Region,o.OrderDate,o.Amount
FROM Regions r JOIN #Orders o ON o.Region=r.Region
WHERE o.OrderDate>='20250701' AND o.OrderDate<'20250801'
UNION ALL
SELECT OrderId,Region,OrderDate,Amount FROM #Orders
WHERE Region IS NULL AND OrderDate>='20250701' AND OrderDate<'20250801';

Test the date-first alternative separately

For the isolated alternative, OrderDate becomes the leading key. Region and Amount remain included output columns. The clustered OrderId remains available through the nonclustered index. Keep the same filter and returned fields when comparing plans. I first remove the two extra orders, so the numbers match the earlier phases.

DELETE FROM #Orders WHERE OrderId>50000;
DROP INDEX IX_RegionDate ON #Orders;
CREATE INDEX IX_Date
 ON #Orders(OrderDate) INCLUDE(Region,Amount);
SELECT OrderId,Region,OrderDate,Amount FROM #Orders
WHERE OrderDate>='20250701' AND OrderDate<'20250801';

An extra production index requires a workload decision. Review writes, storage and other queries before adding it. This example replaces one definition for comparison; it does not measure production maintenance costs. I would resist an index added solely for a prettier plan icon.

Compare the complete answer first

Compare all four returned fields in both directions. Group each complete row with COUNT_BIG to preserve multiplicity. Plain EXCEPT discards duplicate counts, so it is insufficient for a general multiset comparison. Equal row totals alone are also insufficient.

Read the lost AP and NULL rows before interpreting performance. Then compare actual plans and IO for equivalent requests. Measurements belong to this example and its tested environment. They cannot establish a universal claim about every trailing-key predicate.

Measured results for this composite index example

I ran these complete queries on SQL Server 2025. All four baseline phases returned 4,247 matching complete tuples.

The date-only plan applied a Predicate while scanning RegionDate. The NE/SW and date-first plans used SeekPredicates without a separate Predicate. Region discovery added a scan and two seek executions.

These are logical reads from the complete query messages, including region discovery in C. The four phases reported zero physical reads. No cache was cleared, and no elapsed-time improvement is claimed. Production writes and index maintenance remain outside this example.

The figures preserve native abbreviated labels and estimated operator-cost percentages. Their elapsed values describe one execution, rather than a repeatable timing benchmark. The larger C graph has a full-size link for closer reading.

Count the work first, then trust the plan icon. When you are done, clean up.

DROP TABLE IF EXISTS #Orders;
SET STATISTICS IO OFF;

A composite index is not a seek guarantee, it is an ordered structure whose work you measure.

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