Click or drag to resize

SA0072B : Check all Non-Key Index for following specified naming convention. (Batch)

The topic describes the SA0072B analysis rule.

Message

Check all Non-Key Index for following specified naming convention. (Batch)

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.

Scope

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

Parameters
NameDescriptionDefault 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.

Categories

Naming Rules, New Rules

Additional Information

There is no additional info for this rule.

Example Test Script
 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

 MessageLineColumn
1SA0072B : The index name Idx1 does not match the naming convention. The expected name is [CIX_c].230
2SA0072B : The index name IX_VendorID does not match the naming convention. The expected name is [IX_VendorID_Name_Address].513
3SA0072B : The index name IX_ProductVendor_VendorID does not match the naming convention. The expected name is [IX_VendorID].823
4SA0072B : The index name IX_FF does not match the naming convention. The expected name is [IX_FinanceKey_DateKey].1013
5SA0072B : The index name IX_FF does not match the naming convention. The expected name is [IX_FinanceKey_DateKey_OrganizationKey].1313
6SA0072B : The index name AK_UnitMeasure_Name does not match the naming convention. The expected name is [UIX_Name].1620
7SA0072B : The index name AK_Index does not match the naming convention. The expected name is [UIX_C2].2020
8SA0072B : The index name AK_Index does not match the naming convention. The expected name is [UIX_C2].2320
9SA0072B : The index name IX_WorkOrder_ProductID does not match the naming convention. The expected name is [IX_ProductID].2626
10SA0072B : The index name IDX_V1 does not match the naming convention. The expected name is [CIX_OrderDate_ProductID].3230
...
13SA0072B : The index name IX_FF does not match the naming convention. The expected name is [IX_FinanceKey_DateKey].4413
14SA0072B : The index name IX_FF does not match the naming convention. The expected name is [IX_FinanceKey_DateKey_OrganizationKey].4813
See Also

Other Resources