Does the order of columns in an multicolumn non-clustered index matter in SQL Server?

Question

Does the order of columns in a multi-column nonclustered index created for a SQL Server table really matter?

E.g. Is

CREATE NONCLUSTERED INDEX [MultiFieldIndex_1]  
ON [Table_01] ([Column_A],[Column_B],[Column_C],[Column_D])

same as

CREATE NONCLUSTERED INDEX [MultiFieldIndex_1]  
ON [Table_01] ([Column_D],[Column_C],[Column_B],[Column_A]) 

?

Answer 1

YES it matters!

The index might be used, if your query includes the n left-most columns of that index.

So with your first version of index MultiFieldIndex_1, it might be used if you

• use all four columns
• use columns A, B, C
• use columns A, B
• use column A

but it will NOT ever be considered if you use

• just column D
• columns C and D etc.

However, your second version of the index might be used if your specify just D, or D and C - but it will never ever be used if you just specify A and B

Only if you always use all columns that are defined in the index, then the order in which they are defined becomes (almost) irrelevant (there are still some nuances as to ordering by highest selectivity etc. but those are much less important than the fact that an index will not ever be used if you don't specify the n left-most columns in your SELECT statements)