SQL Server: Stop ordering index columns by selectivity

In September 2026 Brent Ozar published a post arguing that the classic interview question about composite index column order has an answer everybody gets wrong, and that for a query filtering two columns with equality the order makes no difference whatsoever. I wanted to test that claim in logical reads on my own instance and dig into the B-tree architecture to see what actually happens.

Building the test

One table, five million rows, and two indexes holding the same two columns in opposite order. The distribution is deliberately skewed, the way real order tables always are.

CREATE TABLE dbo.Orders (
    OrderID     INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    Status      VARCHAR(12)   NOT NULL,
    CustomerID  INT           NOT NULL,
    OrderDate   DATETIME2(0)  NOT NULL,
    Amount      DECIMAL(10,2) NOT NULL
);
GO

INSERT INTO dbo.Orders (Status, CustomerID, OrderDate, Amount)
SELECT TOP 3000000
    'Shipped',
    ABS(CHECKSUM(NEWID())) % 100000 + 1,
    DATEADD(MINUTE, -ABS(CHECKSUM(NEWID())) % 1000000, SYSDATETIME()),
    ABS(CHECKSUM(NEWID())) % 50000 / 100.0
FROM sys.all_columns a CROSS JOIN sys.all_columns b;

INSERT INTO dbo.Orders (Status, CustomerID, OrderDate, Amount)
SELECT TOP 1500000
    'Pending',
    ABS(CHECKSUM(NEWID())) % 100000 + 1,
    DATEADD(MINUTE, -ABS(CHECKSUM(NEWID())) % 1000000, SYSDATETIME()),
    ABS(CHECKSUM(NEWID())) % 50000 / 100.0
FROM sys.all_columns a CROSS JOIN sys.all_columns b;

INSERT INTO dbo.Orders (Status, CustomerID, OrderDate, Amount)
SELECT TOP 500000
    'Cancelled',
    ABS(CHECKSUM(NEWID())) % 100000 + 1,
    DATEADD(MINUTE, -ABS(CHECKSUM(NEWID())) % 1000000, SYSDATETIME()),
    ABS(CHECKSUM(NEWID())) % 50000 / 100.0
FROM sys.all_columns a CROSS JOIN sys.all_columns b;

CREATE NONCLUSTERED INDEX IX_Status_Customer ON dbo.Orders (Status, CustomerID);
CREATE NONCLUSTERED INDEX IX_Customer_Status ON dbo.Orders (CustomerID, Status);
GO

UPDATE STATISTICS dbo.Orders WITH FULLSCAN;
GO

Status holds three distinct values across five million rows and CustomerID holds a hundred thousand. Anyone applying the textbook rule puts CustomerID first, since it slices the table thirty-three thousand times more finely, and writes off IX_Status_Customer as a beginner’s mistake.

Both indexes cover a query that selects only OrderID, because the clustering key sits in every nonclustered leaf. That keeps key lookups out of the read counts and leaves nothing but index access to measure.

Test 1: both columns in the WHERE clause

Let’s run the same query against both indexes, with equality predicates on Status and CustomerID, and compare what comes back.

SET STATISTICS IO ON;

SELECT OrderID
FROM dbo.Orders WITH (INDEX(IX_Status_Customer))
WHERE Status = 'Cancelled' AND CustomerID = 42731;

Table 'Orders'. Scan count 1, logical reads 3, physical reads 0

SELECT OrderID
FROM dbo.Orders WITH (INDEX(IX_Customer_Status))
WHERE Status = 'Cancelled' AND CustomerID = 42731;

Table 'Orders'. Scan count 1, logical reads 3, physical reads 0

Three logical reads each. Same plan shape, same cost, same everything, from two indexes whose leading columns differ in cardinality by a factor of thirty-three thousand.

The seek predicates settle it: both say Prefix, and neither plan carries a residual Predicate section, which means both indexes reach the rows with a single descent on the full composite key rather than filtering one column after the other :

The word that matters is Prefix, and it appears on both sides. SQL Server uses that label when the seek keys form a leading portion of the index key and can be resolved in a single descent through the tree.

This is the heart of the whole thing. A composite index does not store two sorted columns side by side, waiting to be consulted in sequence. It stores one sorted key made of both columns, and Cancelled | 42731 occupies exactly one position inside it. The engine descends once and arrives, and the picture most of us carry, in which the engine narrows the search using the first column, hands a smaller set of rows over to the second and narrows it again, describes something SQL Server never does.

That mental picture is precisely what makes the selectivity rule feel so obviously right. Sort by the column that eliminates the most rows first, the reasoning goes, and the second column has less work left to do. It sounds like common sense, and the only trouble with it is that it describes a two-stage filter which the seek predicates above show to be nowhere in either plan, because the filtering it imagines was already finished the moment the descent landed.

Deep-dive into the nonclustered B-trees

The plans agree. Now let’s see whether the physical structures agree too.

SELECT
    index_level,
    page_count,
    record_count,
    avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(
        DB_ID(),
        OBJECT_ID('dbo.Orders'),
        INDEXPROPERTY(OBJECT_ID('dbo.Orders'), 'IX_Status_Customer', 'IndexID'),
        NULL,
        'DETAILED')
ORDER BY index_level DESC;

SELECT
    index_level,
    page_count,
    record_count,
    avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(
        DB_ID(),
        OBJECT_ID('dbo.Orders'),
        INDEXPROPERTY(OBJECT_ID('dbo.Orders'), 'IX_Customer_Status', 'IndexID'),
        NULL,
        'DETAILED')
ORDER BY index_level DESC;

IX_Status_Customer:

IX_Customer_Status:

Start with the number that ties back to test 1. Both indexes have three levels, and both queries reported three logical reads. Root page, intermediate page, leaf page. The execution plan and the physical structure confirm each other through two completely independent measurements, and neither leaves room for a second lookup step hiding somewhere. Five million rows on both sides, same depth, same cost, and the number of distinct values in the leading column changes neither one.

Now read the tables diagonally. The record_count of any level equals the page_count of the level beneath it: 57 records at level 2 against 57 pages at level 1, then 15’605 records at level 1 against 15’605 pages at level 0. The second index does the same with its own figures, 58 and 58, then 15’589 and 15’589. An upper level therefore holds one entry per child page. One per page, whatever that page happens to contain, which is why the size of these levels tracks the number of pages underneath them and stays completely indifferent to how many distinct values live in the column you chose to lead with.

And that brings us to the comparison the whole article was built for:

Inside the root pages

The DMV tells us each root holds fifty-seven and fifty-eight entries. Let’s look at what those entries actually are.

SELECT
    allocated_page_file_id,
    allocated_page_page_id,
    page_type_desc,
    page_level
FROM sys.dm_db_database_page_allocations(
        DB_ID(),
        OBJECT_ID('dbo.Orders'),
        INDEXPROPERTY(OBJECT_ID('dbo.Orders'), 'IX_Status_Customer', 'IndexID'),
        NULL,
        'DETAILED')
WHERE page_type_desc = 'INDEX_PAGE'
ORDER BY page_level DESC;

SELECT
    allocated_page_file_id,
    allocated_page_page_id,
    page_type_desc,
    page_level
FROM sys.dm_db_database_page_allocations(
        DB_ID(),
        OBJECT_ID('dbo.Orders'),
        INDEXPROPERTY(OBJECT_ID('dbo.Orders'), 'IX_Customer_Status', 'IndexID'),
        NULL,
        'DETAILED')
WHERE page_type_desc = 'INDEX_PAGE'
ORDER BY page_level DESC;

--Run once per index, using the page_level 2 page id returned above
DBCC TRACEON(3604);
DBCC PAGE('IndexOrderDemo', 1, <root_page_id_for_both_index>, 3);

IX_Status_Customer:

IX_Customer_Status:

Several things show up in these dumps, and each of them says the same thing about how the key is treated.

Row 0 carries NULL in every key column, which means an index record holding no key whatsoever, pointing at the leftmost child page and covering every row that sorts below the first boundary anywhere in the page.

Every other entry carries all three key columns, OrderID included, because the clustering key forms part of every nonclustered key. No entry ever names a single column. Each one identifies one specific row out of five million, which is exactly what a boundary between two pages has to do.

In IX_Status_Customer, the same status value appears in entry after entry. Half a million Cancelled rows span roughly 1’560 leaf pages and therefore about six intermediate pages, so the value contributes six separate boundaries.

What about statistics?

Navigation is one thing, description is another. Run DBCC SHOW_STATISTICS on both indexes and the density vectors tell the story of this whole article in five numbers:

DBCC SHOW_STATISTICS('dbo.Orders', 'IX_Status_Customer');

DBCC SHOW_STATISTICS('dbo.Orders', 'IX_Customer_Status');

The single-column entries are worlds apart, 0.3333333 for Status against 1E-05 for CustomerID. The two-column entries are identical to the last digit, 3.340538E-06 on both, because the density of the full prefix counts the same combinations whichever way round you write them. Furthermore, both previous execution plans above show 7 of 17, seven rows actually returned against seventeen expected, and seventeen is not a coincidence: 5’000’000 multiplied by the two-column density of 3.340538E-06 gives 16.7. Since that density is identical in both indexes, so is the estimate.

The histograms for the first column have nothing in common. IX_Status_Customer gets three steps with exact row counts, 500’000 Cancelled, 1’500’000 Pending, 3’000’000 Shipped. IX_Customer_Status gets 21 steps across a hundred thousand customer numbers, with around 50 rows per step. A histogram only ever describes the leading column, so each index is blind to the other’s.

That matters the moment a query supplies one column instead of both, and it is worth knowing before you assume the two indexes are interchangeable for every purpose. It changes nothing for equality on the full key, which is what this article measured.

What to take from this

For equality predicates covering the full key, the two indexes are interchangeable. Three logical reads each, three levels each, and root pages holding 57 entries against 58, from two indexes whose leading columns sit thirty-three thousand apart in cardinality. Brent was right, and the plan, the DMV and the pages all say so independently.

The reason sits in the seek predicates. Prefix on both sides, no residual predicate on either, one descent to one composite key. SQL Server treats the columns as a single sorted value, so there is no first column doing the heavy lifting and no second column mopping up.

None of which makes column order free. Drop the leading column and the two part company immediately: WHERE CustomerID = 42731 alone seeks on one index and scans all 15’605 leaf pages on the other. Add an inequality and only the leading range predicate stays seekable, since a range cannot be resolved by a single descent. The seek finds the boundary and reads forward, everything after that column becomes a residual predicate evaluated row by row, and you can watch it appear as a Predicate section under the seek that was absent from both plans above.

So order your key columns by what your queries supply. Equality columns first, then the single range predicate that eliminates the most rows, because only the first of them gets a real seek. That is the one place selectivity earns its keep.

One last thing worth saying plainly: this article compared two indexes on a single query, and no index ever exists to serve a single query. The right order for IX_Status_Customer or IX_Customer_Status depends on everything else your application runs against that table. Which columns appear alone, which appear together, which carry ranges. That is the analysis worth doing, and counting distinct values will never be a substitute for it.

Next time you are ordering the columns of a composite index, write down the two or three query shapes that actually matter in your workload, check which of them the key prefix can serve, and let that decide the order. If you find yourself counting distinct values instead, stop, because you are answering a question nobody asked.