Sql-server – Should order of column in where clause be same as the index order

sql serversql-server-2008

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

Best 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.

Related Solutions

Sql-server – Is it correct, order of where clause doesn’t matter when it is used with join

The order of items in the where clause should not make a difference, especially if you use the preferred join syntax as follows:

select a.col1, 
       b.col2 
 from table1 a 
 join table2 b on b.col1 = a.col1
where a.col3 = 10

This keeps the join conditions separated from the filters.

Sql-server – Non-Clustered Index key columns to cover a variable where clause

That's a great question.
And there are good answers.

The engine definitely will use the index even if you don't use every key column.
That's especially so if they are in order, as you are talking about.
(can anyone else speak to different orders of key columns?)

You will benefit just fine from selecting just on the first column alone as a key, or multiple columns.
What will make a difference - for any index - is staying inside the INCLUDEd columns.
No matter how many key columns you use in your Where, the performance hit for having to go back to the primary key for additional columns can be huge as it doubles the "operations".

When it comes to dealing with performance vs. size, you have the same problem as with any index.
Since you know you want the same columns returned in all cases, if you are READ focused, you will probably want to the index with all 6, if you INCLUDE everything.
It will certainly save you db size compared to making both indexes.

On WRITE, you obviously have a bigger burden with a larger index. That is a significant additional amount of sorting.
If you do just one row inserted at a time, maybe it won't hardly matter at all.
If you do bulk inserts, you'll definitely want to test the two indexes to see the write performance for your actual inserts.

Best Answer

Related Solutions

Sql-server – Is it correct, order of where clause doesn’t matter when it is used with join

Sql-server – Non-Clustered Index key columns to cover a variable where clause

Related Question