Post reply

Recreating Indexes

  • October 15, 2010 at 5:35 am

    #149024

    Hi,

    Can someone please advise on the best way to drop and recreate indexes?

    Thanks

  • October 15, 2010 at 6:52 am

    #1237477

    Maybe I don't understand the question.

    DROP INDEX myschema.mytable.myindex

    This usually does the job. Then you can create the index.

    If you mean, what's the best mechanism for determining which indexes need to be rebuilt, that's a different question. Or, do you mean you need to drop all the indexes as part of a data load, then recreate them. Again, different question.

    Apart from dropping indexes, what is it that you're trying to do?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • October 15, 2010 at 9:13 am

    #1237633

    If you already have indexes and are looking to drop and recreate those indexes ,then from a performance point of view, the best way to do it is to use the drop_existing option with the

    CREATE INDEX statement

  • October 15, 2010 at 9:26 am

    #1237642

    @ TST1...

    this article describes a good way to do that.

    http://www.mssqltips.com/tip.asp?tip=1018

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • October 15, 2010 at 9:39 am

    #1237651

    vky3 (10/15/2010)

    If you already have indexes and are looking to drop and recreate those indexes ,then from a performance point of view, the best way to do it is to use the drop_existing option with the

    CREATE INDEX statement

    How about just disable / enable?

    No need to script the definition and keep it in sync as the indexes evolve.

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

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