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