patternsqlModerate
Should order of column in where clause be same as the index order?
Viewed 0 times
sameorderthecolumnwhereshouldindexclause
Problem
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
and fired the query
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 !!
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 !!
Solution
So, my question is does the order of columns in Where clause matters
No. The order of columns in the
No. The order of columns in the
WHERE clause does not matter at all.Context
StackExchange Database Administrators Q#24197, answer score: 15
Revisions (0)
No revisions yet.