January 19, 2006 at 2:06 am
Hi,
A table with columns colA, colB, colC, colD, colE, colF
has an index on colA, colB, colC.
Does the folowing query use this index
SELECT * FROM A WHERE colC = 1 and colE = 1 and colA = 1
So question is does the column order in the where clause matter..?
Cheers,
Jan
January 19, 2006 at 2:36 am
Hi Jan - I think it will. To check put your query in query analyzer and generate the expected execution plan. You should see index seek operations.
January 19, 2006 at 3:36 am
try and match your query criteria to the same order as your compound index order, as the qeuery optimiser should be more likely to pick it up and (for the main part) the indexes are more readable when you try and figure out why you're query runs slowly
check the execution plans in QA as already suggested and make sure statistics are up to date on the table (as out of data statistics means the optimiser may choose to ignore the index)
MVDBA
January 19, 2006 at 4:17 am
If you have a compound index on cols A,B,C and your where clause contains ColA then it will use the index.
If your where clause doesn't contain A then it won't
January 19, 2006 at 10:25 am
if you want to increase the chance that sql server uses an index; only select the necessary fields instead of select *.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply