SQL SERVER – Renaming Index – Index Naming Conventions
- by pinaldave
If you are regular reader of this blog, you must be aware of that there are two kinds of blog posts 1) I share what I learn recently 2) I share what I learn and request your participation. Today’s blog post is where I need your opinion to make this blog post a good reference for future.
Background Story
Recently I came across system where users have changed the name of the few of the table to match their new standard naming convention. The name of the table should be self explanatory and they should have explain their purpose without either opening it or reading documentations. Well, not every time this is possible but again this should be the goal of any database modeler. Well, I no way encourage the name of the tables to be too long like ‘ContainsDetailsofNewInvoices’. May be the name of the table should be ‘Invoices’ and table should contain a column with New/Processed bit filed to indicate if the invoice is processed or not (if necessary). Coming back to original story, the database had several tables of which the name were changed.
Story Continues…
To continue the story let me take simple example. There was a table with the name ’ReceivedInvoices’, it was changed to new name as ‘TblInvoices’. As per their new naming standard they had to prefix every talbe with the words ‘Tbl’ and prefix every view with the letters ‘Vw’. Personally I do not see any need of the prefix but again, that issue is not here to discuss. Now after changing the name of the table they faced very interesting situation. They had few indexes on the table which had name of the table. Let us take an example.
Old Name of Table: ReceivedInvoice
Old Name of Index: Index_ReceivedInvoice1
Here is the new names
New Name of Table: TblInvoices
New Name of Index: ???
Well, their dilemma was what should be the new naming convention of the Indexes. Here is a quick proposal of the Index naming convention. Do let me know your opinion.
If Index is Primary Clustered Index: PK_TableName
If Index is Non-clustered Index: IX_TableName_ColumnName1_ColumnName2…
If Index is Unique Non-clustered Index: UX_TableName_ColumnName1_ColumnName2…
If Index is Columnstore Non-clustered Index: CL_TableName
Here ColumnName is the column on which index is created. As there can be only one Primary Key Index and Columnstore Index per table, they do not require ColumnName in the name of the index. The purpose of this new naming convention is to increase readability. When any user come across this index, without opening their properties or definition, user can will know the details of the index.
T-SQL script to Rename Indexes
Here is quick T-SQL script to rename Indexes
EXEC sp_rename N'SchemaName.TableName.IndexName', N'New_IndexName', N'INDEX';
GO
Your Contribute Please
Well, the organization has already defined above four guidelines, personally I follow very similar guidelines too. I have seen many variations like adding prefixes CL for Clustered Index and NCL for Non-clustered Index. I have often seen many not using UX prefix for Unique Index but rather use generic IX prefix only.
Now do you think if they have missed anything in the coding standard. Is NCI and CI prefixed required to additionally describe the index names. I have once received suggestion to even add fill factor in the index name – which I do not recommend at all.
What do you think should be ideal name of the index, so it explains all the most important properties? Additionally, you are welcome to vote if you believe changing the name of index is just waste of time and energy.
Note: The purpose of the blog post is to encourage all to participate with their ideas. I will write follow up blog posts in future compiling all the suggestions.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Index, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
