If you’re getting SQL Server error 7999 that reads something like “Could not find any index named ‘IX_LastName’ for table ‘Employees’“, it appears that you’re trying to create an index with the DROP_EXISTING argument ON, but there is no existing index of that name.

When we set the DROP_EXISTING argument to ON, SQL Server tries to look for the index to drop before it recreates it with the new definition. If it doesn’t exist, then we get the error.

To fix this issue, either check that you’re using the correct name for the index, or disable the DROP_EXISTING option.

Example of Error

Here’s an example of code that produces the error:

CREATE NONCLUSTERED INDEX IX_LastName ON Employees(LastName,FirstName)
WITH DROP_EXISTING;

Output:

Msg 7999, Level 16, State 9, Line 1
Could not find any index named 'IX_LastName' for table 'Employees'.

Here, I used WITH DROP_EXISTING when trying to create an index. This option enables us to recreate an index that already exists (typically to change its definition). What SQL Server does is drops the index first, and then creates it again with the new definition.

But if the index doesn’t exist, then it returns this error.

Note that WITH DROP_EXISTING is the same as WITH (DROP_EXISTING = ON). They both look for an existing index with that name and drop it.

So we’ll get the same error if we use that expanded syntax:

CREATE NONCLUSTERED INDEX IX_LastName ON Employees(LastName)
WITH (DROP_EXISTING = ON);

Output:

Msg 7999, Level 16, State 9, Line 1
Could not find any index named 'IX_LastName' for table 'Employees'.

Regardless of the syntax we use, the problem is the same; we’re trying to drop an index that doesn’t exist.

Solution 1

The first thing we should do is check to make sure we’ve got the right name for the index that we’re trying to recreate. If not, then change it.

For example:

CREATE NONCLUSTERED INDEX IX_FullName ON Employees(LastName,FirstName)
WITH DROP_EXISTING;

Output:

Commands completed successfully.

Here, I changed the index name from IX_LastName to IX_FullName, as it turned out that I had been trying to recreate the wrong index.

Solution 2

If you’re trying to create the index for the first time (i.e. you’re not even trying to recreate an existing index), then you should disable the DROP_EXISTING option.

You can do this either explicitly:

CREATE NONCLUSTERED INDEX IX_LastName ON Employees(LastName)
WITH (DROP_EXISTING = OFF);

Output:

Commands completed successfully.

Or implicitly (by removing the DROP_EXISTING argument altogether):

CREATE NONCLUSTERED INDEX IX_LastName ON Employees(LastName);

Output:

Commands completed successfully.

This works because the default setting for DROP_EXISTING is OFF.