While trying to decide on a SQL naming convention for indexes I stumbled on an excellent post from Narayana Vyas Kondreddi on database object naming conventions.
Just like triggers, indexes also can’t exist on their own and they are dependent on the underlying base tables. So, again it makes sense to include the ‘name of the table’ and ‘column on which it’s built’ in the index name. Further, indexes can be of two types, clustered and nonclustered. These two types of indexes could be either unique or non-unique. So, the naming convention should take care of the index types too.
Vyas’Â resulting convention and examples were a great guide with helping me decide a standard for index naming conventions. Listed below are his examples, that I modified slightly and chose to adopt for my own use as well:
Table name + Column name(s) + Unique/Non-uniqueness + Clustered/Non-clustered
Name the unique, clustered index on the TitleID column of Titles table as shown below:
Titles_TitleID_U_CIDX
Name the unique, nonclustered index on the PubID column of Publishers table as shown below:
Publishers_PubID_U_NIDX
Name a non-unique, non-clustered index on the OrdeID column of OrderDetails table:
OrderDetails_OrderID_NU_NIDX
Indexes can be composite too, meaning, an index can be built on more than one column. In this case, just concatenate the column names together. Name a composite, unique, clustered index on OrderID and OrderDetailID columns of OrderDetails table:
OrderDetails_OrderIDOrderDetailID_U_CIDX