Nonclustered index with include vs nearly same Nonclustered index without; Multiple query coverage

I have two existing indices in a DB as below

  1. CREATE NONCLUSTERED INDEX IndexTable1 ON Table (fkAnothertable)

  2. CREATE NONCLUSTERED INDEX IndexTable2 ON Table (fkAnothertable) INCLUDE (pkTable)

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?

Answers


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.


Need Your Help

How may I scroll a UITableView to the second UITextField when the first UITextField is being edited?

ios objective-c uitableview

Let us assume that we have a Login Screen for an app. The Login Screen implements a UITableViewController, which contains a UITableView. The UITableView has 1 section, and the section contains two ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.