Why SQL Server Lets You Create Redundant Indexes

Q: Why does SQL Server let me create the same (i.e., completely identical) index more than once?

A: I agree, this behavior is annoying, and if it were completely up to me, I wouldn't allow it. At a minimum, I wish there were a configuration option that you could turn on that would prevent this behavior from happening. However, I do understand the backward compatibility reason for this behavior.

If an index is explicitly named in a hint like

   SELECT …    FROM table WITH (INDEX (indexname))    WHERE …

the query would fail if the index were dropped, which could break existing applications. I know that index hints aren't generally recommended, but they still must be supported and work. Therefore, SQL Server can’t drop an existing (and duplicate) index. But, why does SQL Server allow new (and duplicate) indexes to be created? Again, you could make the argument that scripts could have these problems in them already and code could be written to use these index "hints." So, ultimately, it’s been a bad practice in the past and to make sure that no applications are broken, the bad practice needs to be supported now. Don’t get me wrong, I don’t think the practice should be allowed, but that’s the reason it is.