SQL Server Performance

Duplicate index

Discussion in 'T-SQL Performance Tuning for Developers' started by flachance, Mar 28, 2003.

  1. flachance New Member

    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
  2. bradmcgehee New Member

    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
  3. Chappy New Member

    It might be worth inspecting the queries before going ahead with this. <br />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.<br /><br />In all likelihood this wont be a problem for you. <br />But then I never assumed it would be a problem for me!<br /><br /><br />
  4. bambola New Member

    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.

Share This Page