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

The topic describes the SA0072B analysis rule.

Message

Check all Non-Key Index for following specified naming convention

Description

The rule checks the type name in CREATE INDEX statements.

The NamePattern variable can be used to select or configure the desired pattern which will be used to check the object name.

Regular expression patterns can be used, but the pattern must be prefixed with ‘regexp:’ string in order to be used as a matching regular expression.

How to fix

Review the index name and rename it according to the naming convention.

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.
Categories

Naming Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);  
 2
 3CREATE INDEX IX_VendorID ON ProductVendor (VendorID);  
 4CREATE INDEX IX_VendorID ON dbo.ProductVendor (VendorID DESC, Name ASC, Address DESC);  
 5CREATE INDEX IX_VendorID ON Purchasing..ProductVendor (VendorID);  
 6
 7CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing..ProductVendor (VendorID);  
 8
 9CREATE INDEX IX_FF ON dbo.FactFinance ( FinanceKey ASC, DateKey ASC );  
10
11--Rebuild and add the OrganizationKey  
12CREATE INDEX IX_FF ON dbo.FactFinance ( FinanceKey, DateKey, OrganizationKey DESC)  
13WITH ( DROP_EXISTING = ON );  
14
15CREATE UNIQUE INDEX AK_UnitMeasure_Name   
16    ON Production.UnitMeasure(Name);  
17
18
19CREATE UNIQUE INDEX AK_Index ON #Test (C2)  
20    WITH (IGNORE_DUP_KEY = ON);  
21
22CREATE UNIQUE INDEX AK_Index ON #Test (C2)  
23    WITH (IGNORE_DUP_KEY = OFF);  
24
25CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID  
26    ON Production.WorkOrder(ProductID)  
27    WITH (FILLFACTOR = 80,  
28        PAD_INDEX = ON,  
29        DROP_EXISTING = ON);  
30
31CREATE UNIQUE CLUSTERED INDEX IDX_V1   
32    ON Sales.vOrders (OrderDate, ProductID);  
33
34CREATE NONCLUSTERED INDEX IX_Address_PostalCode  
35    ON Person.Address (PostalCode)  
36    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);  
37
38CREATE CLUSTERED INDEX IX_PartTab2Col1  
39ON PartitionTable1 (Col1)  
40WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1),  
41    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4 ) ) ;  
42
43CREATE INDEX IX_FF ON dbo.FactFinance (  
44    FinanceKey ASC, DateKey ASC );  
45
46--Rebuild and add the OrganizationKey  
47CREATE INDEX IX_FF ON dbo.FactFinance (  
48    FinanceKey, DateKey, OrganizationKey DESC)  
49WITH ( DROP_EXISTING = ON );
Analysis Results
  Message Line Column
1 SA0072B : The index name Idx1 does not match the naming convention. The expected name is [CIX_c]. 2 30
2 SA0072B : The index name IX_VendorID does not match the naming convention. The expected name is [IX_VendorID_Name_Address]. 5 13
3 SA0072B : The index name IX_ProductVendor_VendorID does not match the naming convention. The expected name is [IX_VendorID]. 8 23
4 SA0072B : The index name IX_FF does not match the naming convention. The expected name is [IX_FinanceKey_DateKey]. 10 13
5 SA0072B : The index name IX_FF does not match the naming convention. The expected name is [IX_FinanceKey_DateKey_OrganizationKey]. 13 13
6 SA0072B : The index name AK_UnitMeasure_Name does not match the naming convention. The expected name is [UIX_Name]. 16 20
7 SA0072B : The index name AK_Index does not match the naming convention. The expected name is [UIX_C2]. 20 20
8 SA0072B : The index name AK_Index does not match the naming convention. The expected name is [UIX_C2]. 23 20
9 SA0072B : The index name IX_WorkOrder_ProductID does not match the naming convention. The expected name is [IX_ProductID]. 26 26
10 SA0072B : The index name IDX_V1 does not match the naming convention. The expected name is [CIX_OrderDate_ProductID]. 32 30
…
13 SA0072B : The index name IX_FF does not match the naming convention. The expected name is [IX_FinanceKey_DateKey]. 44 13
14 SA0072B : The index name IX_FF does not match the naming convention. The expected name is [IX_FinanceKey_DateKey_OrganizationKey]. 48 13
See Also

Other Resources

Analysis Rules