PrismitaLEARN · PRACTICE · GROW

What is a composite index, and does column order matter?

SQL Server Indexes Advanced 3–5 Yrs5+ Yrs Conceptual 1 min read

Quick Answer

A composite index covers multiple columns in a defined order. Order matters a lot: the index is only useful for queries that filter on a leading prefix of its columns. General rule — put equality-predicate columns first (most selective first), then range columns, then columns only needed for output as INCLUDE.

A composite index spans several columns in a set order, and that order is critical because the index only helps queries that filter on a leading prefix of it. An index on (CustomerId, OrderDate) is great for filtering by customer, or customer plus date, but useless for filtering by date alone. The design rule is: equality columns first, most selective first, then a range column, and columns you only need in the output go in the INCLUDE list so they don't widen the key.

  • An index on (A, B, C) helps queries filtering on A, on A, B, or on A, B, C — but not a query filtering only on B or C.
  • WHERE A = @a AND B > @b — index (A, B) seeks on A then ranges on B. Reversing to (B, A) loses the seek.
  • A range column mid-index “stops” the seek — columns after it can only filter within the range, not seek.
  • Use INCLUDE for columns you only SELECT, so the index covers the query without bloating the key.
All SQL Server questions More on Indexes