SQL Server › Indexes

How do you decide which columns to choose, and in what order, when creating a composite index covering multiple columns?

Intermediate

Answer

You generally place the column most commonly used in equality comparisons, such as an exact match in a WHERE clause, first in the index, followed by columns used for range comparisons or sorting, since SQL Server can use a composite index most effectively when queries filter on a matching left to right prefix of its columns.

-- Good choice if queries commonly filter by CustomerId first, then sort by OrderDate
CREATE INDEX IX_Orders_Customer_Date
ON Orders(CustomerId, OrderDate);

Real-world example A reporting query that always filters by a specific customer and then sorts by order date benefits significantly from a composite index with CustomerId listed first and OrderDate listed second, matching the query's actual filtering and sorting pattern.

Common follow-ups: What happens if a query only filters on the second column of a composite index?;How many columns is it reasonable to include in a single composite index?

Query Optimization & Plans;Data Types & Schema Design