Nonclustered index with include vs nearly same Nonclustered index without; Multiple query coverage
I have two existing indices in a DB as below
CREATE NONCLUSTERED INDEX IndexTable1 ON Table (fkAnothertable)
CREATE NONCLUSTERED INDEX IndexTable2 ON Table (fkAnothertable) INCLUDE (pkTable)开发者_Go百科
I had a hunch and my research seems to point that any queries that call #1 would be satisfied by #2 and that #1 is wasteful. I couldn't find a definitive answer though.
Is this assumption correct and can I drop #1 and potentially improve performance?
Yes. #2 entirely covers #1 and possibly vice-versa in fact. Is pkTable
your clustered index key? If so that will be included in #1 (at the key level because the non clustered index is not declared as unique).
If pkTable
is not the clustering key then queries seeking on #1
will still be satisfied by #2
but #2
may occupy more pages thus making scans that would have used #1
a tad less efficient.
精彩评论