Drop_existing throws an error if index does not exist.

Question

I'm creating/changing a ton of indexes on a large db. Doing this works if the index already exists.

CREATE UNIQUE CLUSTERED
INDEX [table1_1] ON [dbo].[table1] ([col1], [col2], [col3])
WITH DROP_EXISTING ON [PRIMARY]

But if it does not exist the errors.

So I have changed my script to:

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'table1_1') DROP INDEX [table1].[table1_1]
CREATE UNIQUE CLUSTERED
INDEX [table1_1] ON [dbo].[table1] ([col1], [col2], [col3])
ON [PRIMARY]

So the question is am I using WITH DROP_EXISTING wrong?

Answer 1

Yes, that is a limitation of DROP_EXISTING, it does fail if the index did not already exist! (At least on MS SQL 2000 and 2005)

Reference: http://www.mssqltips.com/tip.asp?tip=1362