34

Do index names have to be unique accross the entire sql server database, or just for that table?

For example, should I name my index: IX_OrderLoadCarrierDelivery_OrderLoadID

for the OrderLoadID column of the OrderLoadCarrierDelivery table. Or should I just name it IX_OrderLoadID

Thanks!

Bob Kaufman
  • 12,864
  • 16
  • 78
  • 107
BrokeMyLegBiking
  • 5,898
  • 14
  • 51
  • 66

4 Answers4

39

They have to be unique for the table or view they were created for.

Here is a reference on msdn that details this.

FTA:

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.

I believe the convention is

IX_FieldName
Community
  • 1
  • 1
Joseph
  • 25,330
  • 8
  • 76
  • 125
  • 2
    I think you mean IX_TableName_FieldName. You're thinking along the lines of a foreign key relationship. – Bob Kaufman Nov 13 '09 at 21:25
  • looks like an FK convention, no? – gbn Nov 13 '09 at 21:25
  • 1
    Unless I'm missing something, in MS-SQL, index names only need to be unique within a given table...? I frequently create "duplicate" names, and indeed just did so, for test purposes (in a MSSQL Express 2005 instance). – mjv Nov 13 '09 at 22:07
  • 1
    @mjv Oh wow, you're right, I was completely wrong on that account. I'll edit my answer and quote a source for verification! – Joseph Nov 13 '09 at 23:12
9

No, per table.

That is, a unique (object_id, name) column pair in sys.indexes rather then just (name) in sys.objects (ignoring schema_id)

I'd also use something like IX_SingleColumn or IX_ParentTable. Adding table is superfluous unlike a default or check constraint, say, that is unique per DB

gbn
  • 422,506
  • 82
  • 585
  • 676
  • 2
    +1 it is per table, indeed. Not sure about all the reps and accepted response stating it is per DB. Are we missing something? sysindexes tables can well have several entries with the same name, provided the id (which relates to the table, in sysobjects) is distinct... – mjv Nov 13 '09 at 22:14
  • 1
    Constraint names are unique [per schema](http://msdn.microsoft.com/en-us/library/ms174979.aspx) (search for "constraint_name"), not database. – Nick Chammas Nov 17 '11 at 23:45
-1

However, if the index it's actually CLUSTERED (or PK) then the name must be unique per schema!

DanielGhita
  • 9
  • 2
  • Constraint names need to be unique per schema. Index names don't. Clustered index and PK constraint are not equivalent. You can create a clustered index that isn't associated with any constraint and the name of that does not have to be unique except in the table – Martin Smith Jul 13 '23 at 07:58
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 21 '23 at 09:36
-6

They have to be unique as everything gets stored in sysobjects with the name as key If you use SQL management studio, it's IX_Table_Field syntax

Kumar
  • 10,997
  • 13
  • 84
  • 134
  • 2
    If the index is created as part of the PRIMARY KEY then the name needs to be unique per database. This is because PK indexes have an entry in sys.objects. – Martin Connell Jul 19 '19 at 10:42