1

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

aduguid
  • 3,099
  • 6
  • 18
  • 37
Psn TW
  • 39
  • 5
  • 3
    Each index must have a unique name. – jarlh Jun 19 '18 at 08:50
  • 2
    From MSDN, index name must have a unique name in table or view, NOT database. Am I right? [From MSDN](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-2017) My index name "IX_Folder" is unique in my "Folders" table. – Psn TW Jun 19 '18 at 08:59
  • @PsnTW, correct, index names need only be unique within a table or view, unless the index enforces a primary key or unique constraint. Try DDL instead of the designer. – Dan Guzman Jun 19 '18 at 11:20
  • Dan Guzman, I can use DDL to create index successfully. It seems that the problem is caused by "Table Designer" of "Microsoft SQL Server Management Studio". Thanks. – Psn TW Jun 20 '18 at 02:36
  • For your information only, version of my "Microsoft SQL Server Management" is 11.0.7001.0 – Psn TW Jun 20 '18 at 02:42

1 Answers1

6

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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Yes, you are correct. Problem is caused by "Table Designer" of "Microsoft SQL Server Management Studio". I can create index successfully by DDL, not "Table Designer". Thanks. – Psn TW Jun 20 '18 at 02:40