Script to Merger duplicate indexes

  • Hello,

    I am trying to firgure out how to merge duplicate indexes using a script; any help will be greatly appreciated.

  • Simply DROP the index you no longer wish to keep.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Okay you need to explain that a bit more clearly -- an Index is an object applied to a table.

    So yes you can sort of merge two indexes into one using a script but why would you be doing that?

    Do you have like a lot of these duplicated scripts through out your database?

    Is there a particular reason you must use a script?

    Might you be actually talking about something else and simply calling it an index incorrectly?

  • EM2 wrote:

    Hello,

    I am trying to firgure out how to merge duplicate indexes using a script; any help will be greatly appreciated.

    You don't.  You have to drop both indexes and create a new one.

    BTW... be real careful... if the index is a unique index, it may be the target of an FK.  Also, not all supposed "dupe" or "mergable" indexes actually are. As with all else, "It Depends".

    --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)

  • So we have some indexes that are very similar; some of the tables seem a bit over indexed. The real question here is, how can we merge the indexes that are similar (create one index out the two similar indexes). I can try to do this manually but with so many tables, the process will take a long time. Having a script that peform this task will minimize the  time required... I hope this gives clarity. Thanks so much.

  • You don't actually have to drop both indexes.  You would, for example, often drop one index and modify the other one.

    I've started on code to do this process but have never finished it.  I still do this mostly by hand, although of course I do have a proc that will generate the code for existing index so it's easy to modify it.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Thank you, Scott. This stored procedure generates code for all indexes or similar/duplicate indexes?

  • For all indexes, or the 1 you tell it to generate (either by index number OR by index name).

    As I said, I've been designing code to do my index checking / consolidation work for me, and generate the needed index statement(s), but I haven't completed the design nor the coding yet.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher you ought to share that here and maybe one of us (or more than one of us) can help you complete that code. That might also help this individual and many others streamline this issue going forward.

    As for too many indexes, yeah we ran into that as well on  few tables but we did not have an over abundance of scripts that needed to be fixed and as such we simply dropped one and adjusted the other (if needed).

    That being said I think they recommend like about 7 or fewer indexes upon a single table in general for efficiency purposes but this kind of also depends on how frequently that table gets added to or updated as that is where a lot of indexes tends to slow things down.

  • @dennis Jensen, thank you!

  • ScottPletcher wrote:

    You don't actually have to drop both indexes.  You would, for example, often drop one index and modify the other one.

    I've started on code to do this process but have never finished it.  I still do this mostly by hand, although of course I do have a proc that will generate the code for existing index so it's easy to modify it.

    Seems contrary to the purpose of combining indexes.  You still end up with 2 indexes instead of 1.

    EDIT, because it came up, there is no way to "modify" an index and that appears to be the point of confusion.  There is a way to CREATE a new index with the "DROP EXISTING" option enabled.

    --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)

  • Jeff Moden based on what ScottPletcher stated I do not see how one would end up with 2 Indexes as he did state you Drop one of the Indexes and modify the other which to me means you had 2 Indexes you drop one leaving with 1 Index and then you modify that remaining Index to accomodate for whatever extras are needed. So perhaps you misread what was posted, if not please explain?

  • As for scripts to "find duplicates", they have this new fangled thing called a "search engine". 😀

    Try the following search and see what you come up with.  I haven't looked any of them so I can't make a recommendation.

    Me?  I usually just look for unused indexes.  I also have some proof in a presentation that (for example) having a Non-Clustered index being keyed exactly the same as the Clustered Index isn't necessarily the bad thing everyone makes it out to be.

    --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)

  • Really Jeff Moden that would be interesting to read (or see) -- your proof in a presentation -- as I find that a rather interesting stance and would greatly like to see that (as I am sure others would be as well). I might be good at SQL but I can guarantee you there are always things that I can learn about it and would love to do just that.

    I am still hoping to see and help with ScottPletcher script that he talked about.

    That being said, I do not see in your response Jeff Moden an answer to the question that I had posed to you about the Index count, but I hope you will expound upon that at some point in time.

  • Are there some people who think that 'duplicate' means 'same or similar'?

    If it's a duplicate, it is exactly the same and can be dropped. Otherwise, it is not a duplicate and I cannot imagine the complexity of a script which somehow works out which similar indexes can (and should) be combined and then scripts out the necessary T-SQL to effect those changes.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 15 posts - 1 through 15 (of 25 total)

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