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