August 25, 2014 at 1:45 am
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
August 25, 2014 at 2:23 am
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
August 25, 2014 at 2:31 am
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
August 25, 2014 at 2:38 am
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
August 25, 2014 at 7:34 am
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.
August 25, 2014 at 7:42 am
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
August 25, 2014 at 11:09 am
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.
August 28, 2014 at 7:16 am
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
August 28, 2014 at 12:31 pm
Hello experts
Help please
August 28, 2014 at 11:16 pm
Guess, this will be an unresolved mystery for me:(
August 28, 2014 at 11:48 pm
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