SQL Server Performance Forum – Threads Archive

Duplicate index

I have two indexes on the same table. Index A is comprised of the following columns:
PropertyID, Description Index B is comprised of the following columns:
PropertyID, Description, Stock, Status Am I right in assuming that if I dropped index A, that the query that might have been using it would simply use index B without any ill effects? Thanks, Francois

Yes, your assumption is correct. This is a common issue in many databases, and contributes to unnecessary disk I/O and wasted SQL Server resources. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

It might be worth inspecting the queries before going ahead with this. I got bitten recently because I had an issue like this, and simply dropped the offending indices. Against all odds, the developer had chosen to add index hints to his query (embedded in client code), meaning they failed as soon as the duplicate index was dropped <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Ironically, the hints werent even needed. In all likelihood this wont be a problem for you. But then I never assumed it would be a problem for me!

I usually run utility stored procedure that search for the index name (or any other string) in other procedures/views (or other database objects) before deleting it. Another thing to consider is the type of index. They might both be non-clustered indexes and in this case you just keep index B. But if one of them is a clustered index, you should evaluate which one of them works better for you. Bambola.

]]>