SA0072A : Check all Non-Key Indexes in the current database for following specified naming convention

The topic describes the SA0072A analysis rule.

Message

Check all Non-Key Indexes in the current database for following specified naming convention

Description

The rule checks the naming of all indexes in the current database which are not part of a key constraint.

Separate name patterns are available for each index type:

– Non-Clustered

– Unique Non-Clustered

– Clustered

– Unique Clustered

– Primary XML

– Secondary XML ( Path,Property and Value)

– Spatial

The following placeholders will be replaced in the pattern during the key testing:

{table_name} – Name of the key’s parent table.

{column_list} – List of the key columns, ordered by key ordinal and separated by underscore(‘_’).

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 ContextOnly scope and is applied only on current server and database schema.

Parameters
Name Description Default Value
ClusteredIndexNamePattern

Clustered non-unique index name patten.

CIX_{table_name}_{column_list}

ClusteredUniqueIndexNamePattern

Unique clustered index name patten.

IX_{table_name}_{column_list}

ColumnsListSeparator

Separator which to be used for separating the columns in the {column_list} placeholder.

_

NonClusteredIndexNamePattern

Non-clustered non-unique index name pattern.

IX_{table_name}_{column_list}

NonClusteredUniqueIndexNamePattern

Non-clustered unique index name pattern.

AK_{table_name}_{column_list}

SpatialIndexNamePattern

Spatial index name pattern.

SIdx_{table_name}_{column_list}

XmlIndexPrimaryNamePattern

Primary XML index name pattern.

PXML_{table_name}_{column_list}

XmlIndexSecondaryTypePathNamePattern

Secondary XML Path index pattern.

XMLPATH_{table_name}_{column_list}

XmlIndexSecondaryTypePropertyNamePattern

Secondary XML Property index pattern.

XMLPROPERTY_{table_name}_{column_list}

XmlIndexSecondaryTypeValueNamePattern

Secondary XML Value index pattern.

XMLVALUE_{table_name}_{column_list}

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix
8 minutes per issue.
Categories

Naming Rules, Code Smells

Additional Information

There is no additional info for this rule.

See Also

Other Resources