SQL Server Interview Question #127

Why does column order matter in a composite index?

Indexes & Query Performance Senior Advanced

Detailed Explanation

SQL Server's B-tree indexes are ordered by their key columns from left to right. The leading key columns therefore strongly influence which predicates can be used as efficient seek predicates and whether the index can satisfy an ORDER BY.

Index key order should be based on actual query patterns, equality/range predicates, selectivity, sorting requirements, and workload—not on a simplistic rule such as always putting the most selective column first.

Code Example

CREATE INDEX IX_Orders_CustomerId_OrderDate
ON dbo.Orders(CustomerId, OrderDate DESC);

SELECT Id, OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerId = 100
ORDER BY OrderDate DESC;