IF EXISTS DROP INDEX ... IF NOT EXISTS CREATE INDEX

  • Lowell (8/13/2012)


    obligatory compatibility nazi post:

    sysindexes should be replaced with sys.indexes, as it's going to be dropped in some future version.

    Good point, thanks.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Lynn Pettis (8/13/2012)


    This is how I would code it. Also note that I am using sys.indexes, not sysindexes.

    IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('schema.tablename') AND NAME ='indexname')

    DROP INDEX indexname ON SCHEMA.tablename;

    GO

    CREATE INDEX indexname ON SCHEMA.tablename(columnlist);

    GO

    Edit: Added batch separators just to be safe. Can't remember if the CREATE INDEX has to be first in a batch and quick look in BOL didn't exactly answer the question. Will need to dig a bit more to be sure.

    Thanks for the post.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • GilaMonster (8/13/2012)


    As for the original question...

    IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'Index name here' AND object_id = OBJECT_ID('Table Name Here')

    DROP INDEX ....

    CREATE INDEX ...

    Thank you Gail.:-)

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Dropping an index by name might not work when there is an index on the same column(s) under a slightly different name.

    Proper solution should include comparing entries in sysindexkeys to desired set of columns.

    If there is an indid having the same set of columns in the same order as in the index you are about to create then you need to find index name by indid and drop that index.

    And of course, you need to check for an index with the same name on the same table.

    But if it exists but has different definitions than you better change the new index name rather than drop the index which is probably very important for some queries.

    _____________
    Code for TallyGenerator

  • Why not simply:

    Create Index IndexName on schema.tablename (columnlist)

    with (drop_existing = on);

    [/quote]

    Because sometime you may want to create an index only if it does not exist. The with drop_existing is nice, but why spend 1 hour to re-create an index on a millions of records?

Viewing 5 posts - 16 through 20 (of 20 total)

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