SA0072B : Check all Non-Key Index for following specified naming convention

Introduction

Inconsistent or unclear index naming can lead to confusion and maintenance challenges.

Description

Proper naming conventions in CREATE INDEX statements are crucial for clarity and maintainability within a SQL Server database. When index names do not follow a consistent pattern, it can become challenging to understand and manage the database, especially in complex environments or team settings.

For example:

-- Example of inconsistent index naming
CREATE INDEX idx_customerName ON Customers (Name);
CREATE INDEX cust_name_index ON Customers (Name);

While both indices might serve the same purpose, their inconsistent naming can cause confusion. A standardized naming pattern helps in easily identifying the index purpose and related table, enhancing overall database readability and maintenance.

Lack of standard naming conventions can increase the risk of errors and miscommunications among team members.
It complicates automated processes or scripts that rely on predictable naming patterns.

How to fix

Ensure index names follow a consistent naming convention to enhance clarity and maintainability.

Follow these steps to address the issue of inconsistent index naming conventions:

1.Identify all indexes in your database that do not follow the desired naming convention. This can be done by querying the sys.indexes system catalog view.

2.Determine a standardized naming convention for your database indexes. A common pattern might include the table name and columns involved, such as IX_TableName_ColumnName .

3.Rename indexes to conform to the selected naming convention using the sp_rename stored procedure. This ensures consistent and predictable names.

4.Update any scripts or automated processes to reflect the new index names, to prevent errors or miscommunications.

For example:

-- Identify inconsistent index names
SELECT name FROM sys.indexes WHERE name NOT LIKE 'IX_%';

-- Rename an index to follow the convention
EXEC sp_rename 'cust_name_index', 'IX_Customers_Name';

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Name	Description	Default Value
NamePattern	Default index name pattern.	IX_{column_list}
ColumnsListSeprator	Separator which to be used for separating the columns in the {column_list} placeholder.	_
UniqueNonClusteredIndexPattern	Name pattern for unique non-clustered indexes.	UIX_{column_list}
UniqueClusteredIndexPattern	Name pattern for unique clustered indexes.	CIX_{column_list}

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

8 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);

CREATE INDEX IX_VendorID ON ProductVendor (VendorID);
CREATE INDEX IX_VendorID ON dbo.ProductVendor (VendorID DESC, Name ASC, Address DESC);
CREATE INDEX IX_VendorID ON Purchasing..ProductVendor (VendorID);

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing..ProductVendor (VendorID);

CREATE INDEX IX_FF ON dbo.FactFinance ( FinanceKey ASC, DateKey ASC );

Analysis Results

	Message	Line	Column
1	SA0072B : The index name Idx1 does not match the naming convention. The expected name is [CIX_c].	1	30
2	SA0072B : The index name IX_VendorID does not match the naming convention. The expected name is [IX_VendorID_Name_Address].	4	13
3	SA0072B : The index name IX_ProductVendor_VendorID does not match the naming convention. The expected name is [IX_VendorID].	7	23
4	SA0072B : The index name IX_FF does not match the naming convention. The expected name is [IX_FinanceKey_DateKey].	9	13

Analysis Rules