229

Is there some standard way to name indexes for SQL Server? It seems that the primary key index is named PK_ and non-clustered indexes typically start with IX_. Are there any naming conventions beyond that for unique indexes?

Eric Ness
  • 10,119
  • 15
  • 48
  • 51

4 Answers4

385

I use

PK_ for primary keys

UK_ for unique keys

IX_ for non clustered non unique indexes

UX_ for unique indexes

All of my index name take the form of
<index or key type>_<table name>_<column 1>_<column 2>_<column n>

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
JSR
  • 6,106
  • 2
  • 20
  • 24
  • 1
    What about nonunique clustered indexes? CX? – Chris Marisic Nov 30 '11 at 14:45
  • 8
    I've never had a need for a non unique clustered index ... I realize that it is possible, but it has never seemed to be the correct course of action to me. – JSR Dec 01 '11 at 16:27
  • 5
    According to the answer given here http://stackoverflow.com/questions/1401572/what-are-differences-between-index-v-s-key-in-mysql KEY and INDEX are synonyms. So it should be no need to have different prefixes for unique keys and unique indexes? – skjerdalas Dec 17 '14 at 10:19
  • 2
    Its a logical difference, I use UniqueKey if there will be a foreign key reference, otherwise I use a UniqueIndex. – JSR Jan 08 '15 at 15:17
  • 1
    Why include the table name when two tables can have the same index name? i.e. uniqueness is not needed. – Tahir Hassan Apr 11 '16 at 14:57
  • 2
    Tahir Hassan: I think you are mistaken, Sql Server indexes must have a unique name. In addition, I like being able to look at the index name and see which table or view it belongs to. – JSR Apr 11 '16 at 15:22
  • I'd replace "UK" and "UX" with "UQ", UniQue. – xxxo Apr 29 '16 at 11:02
  • Just googled this question while looking for proper convention for non-unique clustered... And the are still no answer :( – Dmitry Gusarov Sep 14 '16 at 16:29
  • I came here on the same query. We have them a lot as pretty much all our PK are all UUID's but currently all of them are IX_ which doesn't feel right to me. I'm going to go for CX like Chris suggested! :) – caveman_dick Dec 21 '16 at 15:39
  • If table names in the database happen to have underscores in them (as you might find if you are working with a framework such as Ruby on Rails), I add an extra underscore after the table name: IX_table_name__col1_col2 And if column names have underscores, I might add an extra underscore between them as well. – Mark Schneider Nov 17 '17 at 22:41
  • What about included columns? Do you include them in the naming format? – SE1986 Mar 12 '18 at 12:10
  • Using the type of index in the name of the index seems like an anti-pattern. – Rasive Mar 31 '21 at 18:09
35

I usually name indexes by the name of the table and the columns they contain:

ix_tablename_col1_col2
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
13

Is it worth a special prefix for indices associated with foreign keys? I think so, since it reminds me that indices on foreign keys are not created by default, and so it is easier to see if they are missing.

For this, I am using names that match the name of the foreign key:

FK_[table]_[foreign_key_table]

or, where multiple foreign keys exist on the same table

FK_[table]_[foreign_key_table]_[foreign_key_field]
Christian Specht
  • 35,843
  • 15
  • 128
  • 182
Stuart Steedman
  • 141
  • 1
  • 2
1

I know a old topic but thought I'd throw in my 2cents worth

  • PKC_ Primary Key, Clustered
  • PKNC_ Primary Key, Non Clusterd
  • NCAK_ Non Clustered, Unique
  • CAK_ Clustered, Unique
  • NC_ Non Clustered

Example;

NCAK_AccountHeader_OrganisationID_NextDate

Where NCAK : Non Clustered, Unique, AccountHeader : Table and OrganisationID_NextDate : Columns.

Pixelated
  • 1,531
  • 3
  • 15
  • 35