Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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. Query plan

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 !!

share|improve this question
3  
Doesn't the execution plan from you test answer the question? –  ypercube Sep 12 '12 at 15:39
    
Yes !! But I wanted some expert opinion and in cases this can fail . Thanks !! –  Egalitarian Sep 12 '12 at 15:40
1  
Similar question for MySQL: In MySQL, does the order of the columns in a WHERE clause affect query performance? SQL-Server's optimizer is a few levels higher than the MySQL one. –  ypercube Sep 12 '12 at 15:42

3 Answers 3

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

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.