Why am I allowed to have two indexes with the same name?

Question

I have a big .sqlproj project. In one .sql file, I have one table definition:

CREATE TABLE [dbo].[TableOne] ( [ColumnName] UNIQUEIDENTIFIER NULL ); GO CREATE UNIQUE CLUSTERED INDEX [TableOneIndex] ON [dbo].[TableOne]([ColumnName] ASC; 

In another .sql file, I have another table definition:

 CREATE TABLE [dbo].[TableTwo] ( [ColumnName] UNIQUEIDENTIFIER NULL ); GO CREATE UNIQUE CLUSTERED INDEX [TableOneIndex] ON [dbo].[TableTwo]([ColumnName] ASC; 

Note that both indexes are called TableOneIndex . However, the project builds well and deploys well.

How can this be legal?

Answer 1

They have the same name in the SYS.INDEX tables, but they have different OBJECT_ID .

Look at sys.tables

  SELECT * FROM SYS.TABLES WHERE NAME LIKE 'TABLE%' 

and then do:

  SELECT * FROM SYS.INDEXES WHERE OBJECT_ID IN (245575913 ,277576027) 

If the object identifier is the identifier from the sys.tables table related to TableOne and TableTwo

Answer 2

The CREATE INDEX specifications explain this:

index_name Is the name of the index. Index names must be unique in the table or view, but not necessarily unique in the database . Index names must follow identifier rules.

Answer 3

Since they are two separate tables, and TableTwo NOT NOT a TableOne view, it is perfectly normal to have the same index name for both tables

The index name must be UNIQUE in the table NOT the db hole.