Post reply

Duplicate Nonclustered Index

  • December 21, 2015 at 5:24 pm

    #306356

    Hi,

    When I am searching for duplicate Indexes. I can see most of the tables has exact duplicates but the difference is one is unique non-clustered and another is non unique non-clustered index.

    Which one is really needed?

    When we have already unique non-clustered index on a table then again why we need to create another non unique non-clustered index with same columns?

  • December 21, 2015 at 8:15 pm

    #1847324

    To answer your second question first, someone probably just made a mistake.

    To answer your first question, if you have a unique non-clustered and a non-unique non-clustered index on exactly the same columns in exactly the same order, always keep the unique index because 1) SQL Server likes unique indexes a whole lot and 2) it may be the key for a foreign key in another table and 3) it may be an alternate key (think natural key vs surrogate key).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 22, 2015 at 8:48 am

    #1847466

    Do make sure before you drop the NON-unique non-clustered index that you do two things:

    1) Verify EVERY OPTIONAL PARAMETER for both indexes (pad index, fill factor, etc) to make sure they are truly identical. Use those to come up with the best set of options for the UNIQUE one, which is what you should keep IMHO.

    2) Search EVERY piece of source code that hits the database for the name of the non-unique index BEFORE you drop it. Failure to do so could result in a broken app if someone hard-coded an index hint somewhere.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • December 23, 2015 at 11:41 am

    #1847825

    Understood.

    Thank you

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply