Do I need to use multiple column SQL Server index in the same order as I declare it?

Question

When I declare a clustered index, specifying: column1, column2 and column3 in this order - do I need to use the columns in that same order?

For example, will this use the clustered index mentioned earlier to update multiple rows:

 UPDATE Table1     
   WHERE column3 = 1 
      AND column2 = 1 
      AND column1 = 1

Answer 1

The order you use declare the items in the Where clause, as you have stated, should not make a difference as to whether the database server is able to use an index which covers those columns.

Answer 2

It's true that when you're checking for exact equality, that order does not matter.

But that's not to say that the order in the index does not matter -- perhaps this is what your co-worker was trying to say. For example, if I have a table:

PersonID  FName    LName
--------  -------  -----
1         John     Smith
2         Bill     Jones
3         Frank    Smith
4         Jane     Jackson
...
(assume a significantly large table)

and I define an index on it in the order (LName, FName), that index will necessarily perform differently than an index defined in the order (FName, LName), depending on what the query is.

For example, for the query: SELECT * FROM People WHERE LName = 'Smith', you will most likely get a better plan for the first type of index than for the second type.

Likewise, SELECT * FROM People WHERE FName = 'John' will perform better with the second index structure over the first.

And SELECT * FROM People WHERE FName = 'John' AND LName = 'Smith' will perform identically no matter what order the index is created.