CODEWITHSUNDEEP
sqlsql-server
Psn TW
Psn TW

Reputation: 39

MS SQL Server - Cannot Create Same Index Name on Different Tables

I am creating indexes on two separate tables in the same Database (MS SQL Server), and I got an error saying that an index already exists.

This error does NOT come up again if I changed index name to another.

Please help. Many Thanks.

Screenshot from Microsoft SQL Server Management Studio

screenshot

Upvotes: 1

Views: 2423

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239694

I'd strongly suggest that the visual designer is leading you astray. IIRC, indexes used to have schema-scoped names (back in the 7.0 or 2000 era, I think. Before user/schema separation) and later gained the ability to only need to be unique at an individual table level1.

If you try to create a duplicate index manually, you receive the error:

The operation failed because an index or statistics with name '<name>' already exists on table '<table name>'.

Since that's clearly not the error you're seeing, I strongly suspect that it's old code in the visual designer and yet another reason not to use it.

1Unfortunately, we're in an area where historic documentation from the right period is no longer available from the Microsoft website. It used to be easier to verify these recollections because you could still find the "What's new in SQL Server 2000", etc pages there.

Upvotes: 6

Related Questions