CODEWITHSUNDEEP
sql-server
cnom
cnom

Reputation: 3241

Error "Could not find any index named [IX_MyIndex]" upon creating it

I came before this very weird error:

Msg 7999, Level 16, State 9, Line 12 Could not find any index named 'IX_MyIndex' for table 'dbo.MyTable'.

When running the script to create it!!

CREATE NONCLUSTERED INDEX [IX_MyIndex] ON [dbo].[MyTable] (
    [Field1]
    ,[Field2]
    ) INCLUDE (
    Fields3
    ,Fields4
    ,Fields5
    )
    WITH (
         MAXDOP = 4
         ,DATA_COMPRESSION = PAGE
         ,DROP_EXISTING = ON
        )

What am I missing?

Upvotes: 16

Views: 7377

Answers (2)

Suraj Kumar
Suraj Kumar

Reputation: 5643

Remove the last line and execute it.

CREATE NONCLUSTERED INDEX [IX_MyIndex] 
ON [dbo].[MyTable] 
([Field1],[Field2]) 
INCLUDE (Fields3, Fields4, Fields5)

It is trying to search index with name - IX_MyIndex which is not available. But after creating an index of name IX_MyIndex you can run the same query.

Upvotes: 17

cnom
cnom

Reputation: 3241

With the help of Suraj's answer I found that the problem was the option:

DROP_EXISTING=ON

It does not work as I suspected (drop the index IF exists), instead it tries to find it and drop it!

Removing it did the trick!

Upvotes: 19

Related Questions