Should order of column in where clause be same as the index order?

Question

I was reading this use-the-index-luke.com which explains in detail how indexes work. One of the things this person has re-iterated that the order of indexes matter a lot and to make the query fast the where clause columns should be same as that in index. Today, I was just corroborating this theory and created a table (id int , name nvarchar(100) ) on SQL Server 2008. I inserted some 5000 rows in it and created a index create index abc on test (name, id )

and fired the query

select ID, name 
from test 
where ID = 10
and name = '10'

I was expecting a full table scan to be followed by a select in the query plan but to my surprise the output of plan was a index scan folowed by select.

So, my question is does the order of columns in Where clause matters or does SQL Server re-arranges them as per the index definition ?

Thanks !!

Answer 1

So, my question is does the order of columns in Where clause matters

No. The order of columns in the WHERE clause does not matter at all.

Answer 2

First of all, the DROP statement in the query window clearly wasn't executed, but for the sake of explanation, I'll try to address what you asked.

---

Think of indexes like you would a phone book: the entries are sorted by Last Name, then by First Name. (This is the index key order.)

If I ask you to find all the phone numbers of people with the last name "Davis," you would flip to the appropriate page, and read off the numbers. (This is an index seek.)

If I ask you to find all the phone numbers of people with the first name "Janice," you would have to look at every page to find all of them. (This is an index scan.)

If I give you both a first name and a last name, you would be able to find that entry very quickly, just like the first example, because it's obvious you would use the last name to find the right page, then the first name to find the exact entry. It wouldn't matter which order I gave you the information, as long as I gave you both a first name and a last name.

The SQL Server query optimizer works the same way. Based on the index key order, it automatically determines the most efficient way to retrieve the data you've asked for, regardless of the order of the conditions in the WHERE clause.

Answer 3

Order of the columns on the index matter but not in not the order of anded values on the where clause.