If you’re getting SQL Server error 1913 that reads something like “The operation failed because an index or statistics with name ‘IX_Employees_HireDate’ already exists on table ‘Employees’“, it appears that you’re trying to create an index with the same name of one that already exists.

In SQL Server, index names must be unique within a table or view, although they don’t have to be unique within a database. Also, index names must follow the rules of identifiers.

To fix this issue, your course of action will depend on the exact circumstance.

Example of Error

First, here’s an example of code that produces the error:

CREATE INDEX IX_Employees_HireDate ON Employees(HireDate);

This results in the following error message:

Msg 1913, Level 16, State 1, Line 1
The operation failed because an index or statistics with name 'IX_Employees_HireDate' already exists on table 'Employees'.

This error occurred because there was already an index with the same name on the table.

Solution 1

If you’re accidentally trying to create an index that you didn’t realize was already created, you probably don’t need to do anything.

However, you should check the existing index to be sure that it is indeed, the correct index.

Solution 2

If you’re trying to replace an existing index for some reason, then you could always drop the existing one first.

Fortunately, the CREATE INDEX statement has a DROP_EXISTING argument that can help us. This argument enables us to drop and rebuild the existing clustered or nonclustered index with modified column specifications, and keep the same name for the index.

The default value for this option is OFF, but we can turn it ON.

Example:

CREATE INDEX IX_Employees_HireDate 
ON Employees(HireDate)
WITH (DROP_EXISTING = ON);

Output:

Commands completed successfully.

This time it dropped and recreated the index as planned.

Note that there are a few caveats and limitations with regards to the DROP_EXISTING option. See the Microsoft documentation here and here for more info.

If you find that the DROP_EXISTING option isn’t an option, perhaps you need to drop the existing index before recreating it:

DROP INDEX IX_Employees_HireDate ON Employees;
CREATE INDEX IX_Employees_HireDate ON Employees(HireDate);

Solution 3

It’s possible that the existing index is actually designed to do a different job to the one that you’re trying to implement. In this case you might need to change the name of one of the indexes.

EXEC 
    sp_rename @objname = N'dbo.Employees.IX_Employees_HireDate', 
    @newname = 'IX_Employees_FiredDate', 
    @objtype = 'INDEX';

Output:

Caution: Changing any part of an object name could break scripts and stored procedures.

Now that the existing index has been renamed, we can create the new index:

CREATE INDEX IX_Employees_HireDate ON Employees(HireDate);

Output:

Commands completed successfully.

Now that that’s done, it could be time to review your naming conventions.