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