If you are a database modeler or developer, you know the importance of having a clear and self-explanatory naming convention for your database objects. In today’s blog post, I want to discuss the naming convention for indexes in SQL Server and gather your opinions on the matter.

Recently, I came across a situation where users had changed the names of some tables in their database to match their new standard naming convention. While it is important for table names to be descriptive and indicative of their purpose, it is equally important for indexes to have meaningful names as well.

Let’s take a simple example to illustrate the issue. The table “ReceivedInvoices” was renamed to “TblInvoices” as per the new naming standard. However, the indexes on this table still had names that referred to the old table name. This created a dilemma for the users – what should be the new naming convention for indexes?

After considering various options, I propose the following naming convention for indexes:

• If the index is a primary clustered index: PK_TableName
• If the index is a non-clustered index: IX_TableName_ColumnName1_ColumnName2…
• If the index is a unique non-clustered index: UX_TableName_ColumnName1_ColumnName2…
• If the index is a columnstore non-clustered index: CL_TableName

The purpose of this naming convention is to increase readability. When a user comes across an index, they can quickly understand its purpose and properties without having to open its properties or definition.

Here is a T-SQL script that can be used to rename indexes:

EXEC sp_rename N'SchemaName.TableName.IndexName' , N'New_IndexName' , N'INDEX';
GO

Now, I would like to hear your thoughts on this naming convention. Do you think it covers all the important properties of an index? Should there be additional prefixes like NCI and CI to describe the index type? Or perhaps you believe that changing the name of an index is a waste of time and energy?

Please share your ideas and suggestions. Your contributions will help create a comprehensive and widely accepted naming convention for indexes in SQL Server. I will compile all the suggestions and write a follow-up blog post in the future.

Thank you for your participation!