Drop Existing while creating Index

  • Hello Experts,

    The Drop Existing clause while index creation prevents other NonClustered Index to be rebuilt twice.

    Once while dropping and other while creating clustered index.And rebuilts Nonclustered only if key column changes in Clustered index.

    What if I Only fire a drop Clustered Index command and NO Create command ?

    Will it take the drop_Existing value of the clustered index , when it was created ?

    and wont rebuilt Nonclustered index ?

    If Yes then in this case then we have to rebuild NonClustereIndex as they will be still have wrong pointers

  • er.mayankshukla (8/25/2014)


    What if I Only fire a drop Clustered Index command and NO Create command ?

    Then every single nonclustered index on the table will be rebuilt, they have to be because the 'pointer' to the row changes from the clustered index key to a RID

    Drop_existing isn't a persisted setting, it's not remembered. It applies to a specific create index statement only and has no lingering effect after the create completes.

    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
  • Well It means that whenever I fire Drop and create index command with drop_existing option.

    Sql engine waits to check for create command after drop command.

    I am still doubtfull about this.

    Also my question is while creating non clustered index with drop_existing option will have no effect as dropping Nonclustered index will not affect any other index

  • er.mayankshukla (8/25/2014)


    Well It means that whenever I fire Drop and create index command with drop_existing option.

    Sql engine waits to check for create command after drop command.

    No. If you run a DROP INDEX, then run a CREATE INDEX ... WITH DROP_EXISTING, you'll get an error. Using DROP_EXISTING requires that the index exists. It's a drop and create in a single statement.

    Also my question is while creating non clustered index with drop_existing option will have no effect as dropping Nonclustered index will not affect any other index

    It lets you recreate an unique index without leaving an interval between the drop and create where someone could insert a duplicate value

    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
  • I will try to ask my question with giving table structures

    create table sample(id int,name varchar(10))

    create unique clustered index cix on sample(id)

    with (drop_existing = ON)

    Success

    create clustered index cix on sample(id)

    with (drop_existing = ON)

    Fails saying

    Could not find any index named 'cix2' for table 'sample'.

    Why unique clustered is only allowed with drop_existing as ON.

    Kindly explain me this logic

    I have tried a lot to search on google, but couldn't find it.

  • er.mayankshukla (8/25/2014)


    Why unique clustered is only allowed with drop_existing as ON.

    It isn't. You can drop a unique clustered index. You can create a unique clustered index without the DROP_EXISTING clause.

    Besides, this code could not possibly give that error. Look at the names, the name in the error does not match the name in the script.

    create clustered index cix on sample(id)

    with (drop_existing = ON)

    Fails saying

    Could not find any index named 'cix2' for table 'sample'.

    A CREATE INDEX ... WITH DROP EXISTING replaces an existing index. Hence an index of the same name must exist. If one doesn't, then you're just creating a index and you would not use the DROP_EXISTING clause as there isn't an existing 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
  • Thanks Gail,

    Every time when google doesn't works for me, you have an answer 🙂

    I got you point and tested it by examining the query plans.

    Just a last clarification,

    as u stated that for a unique non clustered index drop_existing makes sure that no duplicate value gets inserted during the time interval of Drop and create.

    But drop_existing for a nonunique Non Clustered index should not have any effect.

    as we are not bothered about duplicates in this case.

  • When I am using Drop_existing = on for NonUnique clustered index , I see no change in nonclusted indexes.

    But When I do this for unique CLustered index , I can see Index Insert for Non Clustered index as well

    Kindly explain

  • Hello experts

    Help please

  • Guess, this will be an unresolved mystery for me:(

  • My Bad.

    Drop_Existing will not cause Non clustered indexes to rebuilt , no matter we have unique or nonunique clustered indexes

Viewing 11 posts - 1 through 10 (of 10 total)

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