current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 6 down vote favorite

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

CREATE TABLE Activity (
    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 
( 
    ActivityName
)

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.

share|improve this question

2 Answers 2

up vote 9 down vote accepted

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. :-)

share|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. –  LachlanB 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
up vote 2 down vote

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]

HTH

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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