Sql-server – Does an index with multiple columns make a similar index redundant

indexindex-tuningsql server

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.

Best Answer

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

Related Question