March 6, 2009 at 4:59 am
I'm after some clarification please.
1 index with the following columns
column1, column2, column3, column4, column5
1 index with the following columns
column1, column2, column3, column5, column4
If you query and select the columns above regardless of the order of column4 and column5 it would choose either index with no discrimination as long as you filter on column1, can someone confirm if this statement is correct.
Would the execution plan choose the correct index if you filtered on all columns i.e.
where column1 = 1 and column2 =2 and column3 = 3 and column4 =4 and column5=5
and
where column1 = 1 and column2 =2 and column3 = 3 and column5 =5 and column4=4
My goal is to remove these "duplicate indexes from the tables" as I don't see any advantage.
Thanks.
March 6, 2009 at 5:25 am
If your queries allways provide 5 fields to search, you don't need these 2 indexes, with only one index you can do the process (SELECT, UPDATE, DELETE)
They would be necessary only if in some cases you provide only fields 1,2,3,4 and in other cases you provide only fields 1,2,3,5. But, in these cases you must analyze the need. For example, if fields 1,2,3 are high restrictive (in terms on selection), you could define only a index with these 3 fields and let the SQL make a few bookmark lookups. (Assuming the index is nonclustered)
March 6, 2009 at 6:43 am
It's the leading edge that makes the most difference, yes. I'd drop one of the two indexes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply