SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Drop Existing while creating Index


Drop Existing while creating Index

Author
Message
er.mayankshukla
er.mayankshukla
SSC-Addicted
SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)

Group: General Forum Members
Points: 457 Visits: 478
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86352 Visits: 45232
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


er.mayankshukla
er.mayankshukla
SSC-Addicted
SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)

Group: General Forum Members
Points: 457 Visits: 478
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86352 Visits: 45232
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


er.mayankshukla
er.mayankshukla
SSC-Addicted
SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)

Group: General Forum Members
Points: 457 Visits: 478
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86352 Visits: 45232
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


er.mayankshukla
er.mayankshukla
SSC-Addicted
SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)

Group: General Forum Members
Points: 457 Visits: 478
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.
er.mayankshukla
er.mayankshukla
SSC-Addicted
SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)

Group: General Forum Members
Points: 457 Visits: 478
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
er.mayankshukla
er.mayankshukla
SSC-Addicted
SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)

Group: General Forum Members
Points: 457 Visits: 478
Hello experts
Help please
er.mayankshukla
er.mayankshukla
SSC-Addicted
SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)

Group: General Forum Members
Points: 457 Visits: 478
Guess, this will be an unresolved mystery for meSad
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search