Home Forums SQL Server 2008 T-SQL (SS2K8) IF EXISTS DROP INDEX ... IF NOT EXISTS CREATE INDEX RE: IF EXISTS DROP INDEX ... IF NOT EXISTS CREATE INDEX

  • August 13, 2012 at 2:44 pm

    #1525284

    GSquared (8/13/2012)

    Why not simply:

    Create Index IndexName on schema.tablename (columnlist)

    with (drop_existing = on);

    Because that does not work if the index does not exist.

    Create Index doesnotexist on DBO.Test (ID)

    with (drop_existing = on);

    Msg 7999, Level 16, State 9, Line 1

    Could not find any index named 'doesnotexist' for table 'DBO.Test'.

    Create With drop_existing is equivalent to DROP INDEX ... CREATE INDEX, not IF EXISTS ... DROP INDEX ... CREATE INDEX

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass