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)
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.
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