SQL SERVER – Renaming Index – Index Naming Conventions

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)

About these ads

19 thoughts on “SQL SERVER – Renaming Index – Index Naming Conventions

  1. thankyou very much again for another interesting post. i think the name of the object should depict its functionality or type of work it will do. i dont think that we should think like index or any other object should display its definition in its name rather the purpose should be visible. it will be more understandable.
    like if we want to name an index then it should be like gettopinvoices or in worst case it should be names like getinvoiceid. we can always look into definition for details but for a quick look it be good to know the purpose. its my opinion. i will be more than happy to look forward for better ideas n discuss it. thankyou.

  2. @ Pinal,

    Post was simple yet interesting.

    I follow below standard

    – Indexes
    Clustered Index – Same as Primary Key (99% of time)
    Non-Clustered Index – IX1_TableName, IX2_TableName…..

    – Keys
    Primary Key – PK_TableName
    Foreign Key- FK1_TableName , FK2_TableName …..
    Unique Key – UK1_TableName, UK2_TableName …

    – Constraints
    Check Constraint – CK1_TableName, CK2_TableName …..
    DEFAULT Constraint – Leave to default

    I like to keep it simple, the reason I follow this is to keep the name readable. It gets very clingy to have parent table name and child table name and column name on which foreign key is defined.

    Since Developers/DBA’s are most likely the one to see information at this level, they always have access to definition of these objects as mentioned by “musabumair”

    ~ IM.

  3. At the company where i’m working we are using the below naming standards.

    The purpose we use when we are having 3 or more columns. Because in those cases one indexes a special case. So the actual column names aren’t as imported.

    Primary Key = PK_
    Foreign Key = FK__[_]
    Unique Index = AK_[_]
    Index = IX__
    Default = DF__
    Check = CK__

    Even though the developers and admins do have access to the definitions we found it to save a lot of time if the objects have clear names. It does take some effort/rountine, but thankfully there are DMV’s in case someone was in a “rush” (in other words ignored the naming standard…).

    • We use a similar method, and find it helps for later communication and maintenance. For example, I can talk to another developer about FK_thisnthat instead of having to describe it as “thisnthat which is a FK”. Also, if I forget to mention that FK_thisnthat is an FK, my mistake is covered because the name is FK.

  4. At the company where i’m working we are using the below naming standards.

    The purpose we use when we are having 3 or more columns. Because in those cases one indexes a special case. So the actual column names aren’t as imported.

    Primary Key = PK_Table
    Foreign Key = FK_Table_ParentTable[_Purpose]
    Unique Index = AK_Table[_Column(s),Purpose]
    Index = IX_Table_Column(s),purpose
    Default = DF_Table_Column
    Check = CK_Table_Column(s),purpose

    Even though the developers and admins do have access to the definitions we found it to save a lot of time if the objects have clear names. It does take some effort/rountine, but thankfully there are DMV’s in case someone was in a “rush” (in other words ignored the naming standard…).

    PS: Sorry for the double post, but in the previous one the majority of the naming standard got removed due to the parser :D

  5. Renaming existing indexes can also break code if they are used in hints..! So it’s best to follow naming conventions when creating indexes…

  6. Hi Pinal,

    This is a very good article which will help to learn about the Coding Standard.

    Well, the Index names i use to give CIX-Clustered Index,
    NCIX- Non-Clustered Index
    UCIX–Unique Clustered Index
    UNCIX-Unique Non Clustered Index.

  7. Hi Pinal,
    Nice article, With respect to naming conventions pick a pattern and stay consistent. Avoid names that would conflict with the names of system tables and system stored procedures. With respect to indexes follow the flow of the type of index, Primary Key (PK),Foreign Key (FK), Clustered Index (CI), Non Clustered Index (NCI). Try and produce a best practices document and update it as and when new types are introduced in sql server.

  8. Here is a script I use to rename indexes that don’t follow my naming convention – sorry it’s long:

    SELECT [o].[name] AS ‘Table’
    ,[i].[name] AS ‘Current Index’
    ,CASE WHEN [i].[is_unique] = 1
    AND [i].[is_primary_key] = 0 THEN ‘UI_’
    WHEN [i].[is_primary_key] = 1 THEN ‘PK_’
    ELSE ‘IX_’
    END + [o].[name] + CASE WHEN [i].[is_primary_key] = 0 THEN ‘_’ + [Index_Columns].[index_columns_key]
    ELSE ”
    END AS ‘New Index’
    ,’EXEC sp_rename ”’ + [s].[name] + ‘.’ + [o].[name] + ‘.’ + [i].[name] + ”’, ”’
    + CASE WHEN [i].[is_unique] = 1
    AND [i].[is_primary_key] = 0 THEN ‘UI_’
    WHEN [i].[is_primary_key] = 1 THEN ‘PK_’
    ELSE ‘IX_’
    END + [o].[name] + CASE WHEN [i].[is_primary_key] = 0 THEN ‘_’ + [Index_Columns].[index_columns_key]
    ELSE ”
    END + ”’, ”INDEX”’ AS ‘sp_rename script’
    FROM [sys].[objects] AS [o]
    JOIN [sys].[schemas] AS [s] ON [o].[schema_id] = [s].[schema_id]
    JOIN [sys].[indexes] AS [i] ON [i].[object_id] = [o].[object_id]
    CROSS APPLY ( SELECT LEFT([index_columns_key], LEN([index_columns_key]) – 1) AS [index_columns_key]
    FROM ( SELECT ( SELECT TOP 3
    [c].[name] + ‘_’
    FROM [sys].[index_columns] AS [ic]
    JOIN [sys].[columns] AS [c] ON [ic].column_id = [c].column_id
    AND [ic].[object_id] = [c].[object_id]
    WHERE [ic].is_included_column = 0
    AND [i].[object_id] = [ic].[object_id]
    AND [i].index_id = [ic].index_id
    ORDER BY key_ordinal
    FOR
    XML PATH(”)
    ) AS [index_columns_key]
    ) AS [Index_Columns]
    ) AS [Index_Columns]
    WHERE [o].[type] = ‘u’
    AND [o].[is_ms_shipped] = 0
    AND [i].[type_desc] ‘HEAP’
    AND LEFT([o].[name],1) = ‘W’ — Keyora table
    AND [i].[name] CASE WHEN [i].[is_unique] = 1
    AND [i].[is_primary_key] = 0 THEN ‘UI_’
    WHEN [i].[is_primary_key] = 1 THEN ‘PK_’
    ELSE ‘IX_’
    END + [o].[name] + CASE WHEN [i].[is_primary_key] = 0
    THEN ‘_’ + [Index_Columns].[index_columns_key]
    ELSE ”
    END
    ORDER BY [s].[name]
    ,[o].[name]
    ,[i].[name]

  9. Sir,
    May I ask a question?
    When I create clustered index and unique index on table, the table default sort by unique index?
    CREATE TABLE t2(c1 INT,c2 INT );
    CREATE CLUSTERED INDEX ck_t2 ON t2(c1 ASC);
    CREATE UNIQUE INDEX uk_t2 ON t2(c2 ASC);
    INSERT INTO t2 ( c1, c2 ) VALUES ( 1, 2), (2, 1), (3, 8), (4, 3);
    –WHY DEFAULT SORT BY UNIQUE INDEX?
    SELECT * FROM t2;

  10. I think the name is important to a small degree only.

    I tend to follow the PK_ IX_ convention followed by table name and primary column but I also think there is a case against such convention as sometimes people will infer knowledge of an index from its name and later find out that the index had been altered without the name being changed. This may have been as a result of index hints (even though they are generally a bad idea).

    I tend not to bother with UX_ etc.

    I think naming conventions are great for table names etc due to the order of the items in SSMS and also to enforce conformity and ease of T-SQL. This is particularly notable on Column names such as DateStamp, DateEntered, EntryDate, Entered_timestamp, DateCreated, TimeStamp, LastUpdated etc where people often create multiple variants across many tables and cause huge confusion in the T-SQL but I also think too much effort can be spent in things like index names. There comes a point where you need to think of the more important issue of getting the work done.

    IMHO

    Dave

  11. Hi pinal, In your blog many times i had seen that u use N’. I have search for it and found out that it is basically used with unicode columns. but over here u are using it for executing the stored procedure.

  12. Hi, I have two doubts.
    1. To allocate the memory space for varchar like 128,256,512 means it will allocate the memory like block. is it correct?
    2.Column name like username and strUsername, which one is better username or strusername(str-string, i.e column datatype is prefixed)

  13. Pingback: SQL SERVER – Weekly Series – Memory Lane – #030 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s