Composite Key Order and INCLUDE

An index on (A, B, C) is sorted by A, then within equal A by B, then within equal B by C. It is a phone book: sorted by surname then first name. You can find every Nakamura instantly, and every Nakamura named Kenji instantly. You cannot find every Kenji.

Everything about composite key order follows from that.

The ordering rule

Put the columns in this order:

1. Equality predicates first — columns compared with =. Order among these matters less than people think, though putting the most selective first helps.
2. Then one range predicate — >, <, BETWEEN, LIKE 'x%', IN over a wide set.
3. Then columns needed for ORDER BY / GROUP BY, if the order after the range column happens to match.
4. Everything else goes in INCLUDE, not in the key.

The reason for "one range predicate" is important: once the index seek hits a range column, the columns after it are no longer usable for narrowing the seek. They can only be residual filters applied to whatever rows the range returned. A second range column in the key buys you nothing for seeking.

Applied to a report query

Take a typical reporting query:

SELECT  o.OrderId, o.OrderDate, o.Total, c.CustomerName
FROM    dbo.Orders   AS o                 -- placeholder tables
JOIN    dbo.Customer AS c ON c.CustomerId = o.CustomerId
WHERE   o.RegionId  = @RegionId           -- equality
  AND   o.StatusId  = @StatusId           -- equality
  AND   o.OrderDate >= @FromDate          -- range
  AND   o.OrderDate <  @ToDate            -- range (same column, still one range)
ORDER BY o.OrderDate DESC;

The index that serves it:

CREATE NONCLUSTERED INDEX IX_Orders_Region_Status_OrderDate
    ON dbo.Orders (RegionId, StatusId, OrderDate DESC)
    INCLUDE (CustomerId, Total);

Read that back against the rule: two equality columns, then the range column, and the columns the SELECT needs but never filters on live in INCLUDE. The DESC on OrderDate matches the ORDER BY, so the plan can return rows in order without a Sort operator — worth checking, because a Sort on a large result set is one of the biggest single costs in a report plan and it can spill to tempdb.

Key columns vs INCLUDE columns

| | Key columns | INCLUDE columns | |---------------------|--------------------------------------|------------------------------------| | Stored where | Every level of the B-tree | Leaf level only | | Usable for | Seeking, ordering, residual filters | Covering the SELECT list only | | Size cost | Higher (repeated up the tree) | Lower | | Type restrictions | 900/1700-byte key limit; no LOB | LOB types allowed; much larger cap |

The practical consequence: adding a wide nvarchar(400) column to the key makes the whole tree bigger and slower to traverse. Adding it to INCLUDE costs leaf space only. When in doubt, if you do not filter, join or sort on the column, it belongs in INCLUDE.

Prove it to yourself

DROP TABLE IF EXISTS #Sales;
CREATE TABLE #Sales (
    SaleId    int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    RegionId  int  NOT NULL,
    StatusId  tinyint NOT NULL,
    SaleDate  date NOT NULL,
    Amount    decimal(18,2) NOT NULL,
    Notes     nvarchar(300) NULL
);

INSERT INTO #Sales (RegionId, StatusId, SaleDate, Amount, Notes)
SELECT TOP (300000)
       ABS(CHECKSUM(NEWID())) % 12,
       ABS(CHECKSUM(NEWID())) % 4,
       DATEADD(DAY, -(ABS(CHECKSUM(NEWID())) % 1200), CAST(SYSDATETIME() AS date)),
       (ABS(CHECKSUM(NEWID())) % 200000) / 100.0,
       REPLICATE(N'n', 150)
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;

SET STATISTICS IO ON;

DECLARE @from date = DATEADD(DAY, -60, CAST(SYSDATETIME() AS date)),
        @to   date = CAST(SYSDATETIME() AS date);

-- (a) no useful index: clustered scan
SELECT SaleId, SaleDate, Amount FROM #Sales
WHERE RegionId = 3 AND StatusId = 1 AND SaleDate >= @from AND SaleDate < @to;

-- (b) range column FIRST: seek is wide, then filters
CREATE INDEX IX_bad  ON #Sales (SaleDate, RegionId, StatusId) INCLUDE (Amount);
SELECT SaleId, SaleDate, Amount FROM #Sales
WHERE RegionId = 3 AND StatusId = 1 AND SaleDate >= @from AND SaleDate < @to;

-- (c) equalities first, range last
CREATE INDEX IX_good ON #Sales (RegionId, StatusId, SaleDate) INCLUDE (Amount);
SELECT SaleId, SaleDate, Amount FROM #Sales
WHERE RegionId = 3 AND StatusId = 1 AND SaleDate >= @from AND SaleDate < @to;

SET STATISTICS IO OFF;

Compare logical reads across (a), (b) and (c) in the Messages tab. Version (b) does seek, but it seeks across every region and status in the date range and then discards most of what it read; version (c) navigates straight to the rows that qualify.

Left-anchoring: one index can serve several queries

An index on (RegionId, StatusId, SaleDate) also supports queries filtering on RegionId alone, and on RegionId + StatusId. It does not support a query filtering on StatusId alone — that is asking the phone book for every Kenji.

This is the main lever for keeping index count down. Before adding an index, check whether reordering an existing one would let it serve both queries. Three well-ordered indexes beat nine near-duplicates, and the write cost is a third.

Column order for GROUP BY

If the query aggregates, an index whose key order matches the GROUP BY columns allows a Stream Aggregate (cheap, streaming) instead of a Hash Match (Aggregate) (needs a memory grant, can spill). On a report that groups a million rows, that difference is visible.

Rule of thumb

Equality columns first, then the range column, then anything left over goes in INCLUDE. Once the seek reaches a range predicate, later key columns cannot narrow it any further — they are just residual filters. Check whether reordering an existing index lets it serve both queries before adding a new one, because every extra index is a cost on every insert and update.