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

Question

I have a large .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 indices are called TableOneIndex. Yet the project builds fine and deploys fine.

How can this be legal?

Index names need to be unique per _table_ / _view_ , not the entire DB: http://stackoverflow.com/a/1732007/1583 — Oded, May 20 '13 at 09:43
Both columns are called `ColumnName` and that's also legal :) — AakashM, May 20 '13 at 09:56

score 15 · Answer 1 · answered May 20 '13 at 10:14

15

The CREATE INDEX specifications explain this:

index_name Is the name of the index. Index names must be unique within a table or view but do not have to be unique within a database. Index names must follow the rules of identifiers.

answered May 20 '13 at 10:14

Remus Rusanu

288,378
40
442
569

score 10 · Answer 2 · edited May 20 '18 at 00:58

10

Since they are 2 separate tables and TableTwo IS NOT a view of TableOne, it's perfectly normal to have the same index name on both tables

Index name must be UNIQUE within the table NOT the whole database.

edited May 20 '18 at 00:58

Regular Jo

5,190
3
25
47

answered May 20 '13 at 09:46

Stephan

8,000
3
36
42

score 10 · Accepted Answer · answered May 20 '13 at 09:47

They have the same name in the SYS.INDEX tables however they have complete different OBJECT_ID's.

Look at the sys.tables

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

and then do:

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

Where the object ID's are the ID's from the sys.tables table relating to TableOne and TableTwo

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

3 Answers3