Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Drop Existing while creating Index Expand / Collapse
Author
Message
Posted Monday, August 25, 2014 1:45 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:03 AM
Points: 49, Visits: 138
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
Post #1606979
Posted Monday, August 25, 2014 2:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 39,965, Visits: 36,320
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 2008, MVP
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

Post #1606982
Posted Monday, August 25, 2014 2:31 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:03 AM
Points: 49, Visits: 138
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
Post #1606984
Posted Monday, August 25, 2014 2:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 39,965, Visits: 36,320
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 2008, MVP
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

Post #1606985
Posted Monday, August 25, 2014 7:34 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:03 AM
Points: 49, Visits: 138
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.

Post #1607066
Posted Monday, August 25, 2014 7:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 39,965, Visits: 36,320
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 2008, MVP
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

Post #1607073
Posted Monday, August 25, 2014 11:09 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:03 AM
Points: 49, Visits: 138
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.
Post #1607145
Posted Thursday, August 28, 2014 7:16 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:03 AM
Points: 49, Visits: 138
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
Post #1608265
Posted Thursday, August 28, 2014 12:31 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:03 AM
Points: 49, Visits: 138
Hello experts
Help please
Post #1608383
Posted Thursday, August 28, 2014 11:16 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:03 AM
Points: 49, Visits: 138
Guess, this will be an unresolved mystery for me
Post #1608491
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse