Let's say I have a table that looks like this:

    ActivityID int primary key identity(1,1) ,
    ActivityName nvarchar(10),
    InactiveFlag bit

with an index that looks like this:

CREATE INDEX Activity_Index on Activity 

then for some reason, someone has created a second index:

CREATE INDEX Another_Activity_Index on Activity 
    ActivityName, InactiveFlag

is it definitely safe to delete the first index? Is it just taking up unneccesary disk space? Will the second index cover all cases of the first? The column ordering is definitely "ActivityName" first.


In this case I consider the first index redundant (and less useful). Note that dropping the index will invalidate any plan that references it, so the next time you run any of those queries, you may see a blip in performance due to the required recompile to access the second index instead. While both indexes existed, any query that only needed to act on ActivityName would probably have chosen the first index since it is skinnier.

If the second column in the second index was something wider, then I might be a little less cavalier about my advice to drop the first index. If that column were a wide varchar, for example, then the additional I/O required to use the index when necessary (and when the varchar column were not needed for output or filtering) would actually make the first index (even though it is technically redundant) more desirable to satisfy that type of query.

So, just a long-winded way to say: it depends. :-)

| improve this answer | |
  • So if the second index is a bigger data type than the primary column then I shouldn't delete the index? Is SQL Server clever enough to pick the fastest index? If this is the case then I might need to expand my question to be more specific to my case, I tried to narrow it down to first principles, but I have inherited this mess of a DB and I'm trying to reclaim some disk space in wasted indexes. – Rocklan May 23 '13 at 1:59
  • 1
    Yes, I think more specifics are warranted - not just about the data types of the columns in the indexes, but also typical query patterns. There is no general, blanket, always-applies answer. – Aaron Bertrand May 23 '13 at 2:00
  • @LachlanB Sometimes even if the second column of the second index is a wide varchar, the first one is still redundant and may even be bad for performance, like when you have many DML's (insert/update/delete) which require updating of the indexes. – Roi Gavish Jun 9 '13 at 11:06

They are not duplicate. However, depending on the nature of your queries, they may not both be needed.

Whenever I need to review the definition of a duplicate index I refer to Kimberly Tripp's posts:

http://www.sqlskills.com/blogs/kimberly/how-can-you-tell-if-an-index-is-really-a-duplicate/ http://www.sqlskills.com/blogs/kimberly/removing-duplicate-indexes/

In your case, it would appear that the tree columns for the three indexes would be as follows:

columns_in_tree: [ActivityID] columns_in_leaf: All columns

columns_in_tree: [ActivityName] columns_in_leaf: [ActivityName], [ActivityID]

columns_in_tree: [ActivityName], [InactiveFlag] columns_in_leaf: [ActivityName], [InactiveFlag], [ActivityID]


| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.