Does an index with multiple columns make a similar index redundant?
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. :-)
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