SA0022 : Index type (CLUSTERED or NONCLUSTERED) not specified

It is recommended to specify the index type (CLUSTERED or NONCLUSTERED) when creating an index to ensure clarity and proper indexing behavior.

Description

When creating indexes in T-SQL, it is crucial to specify the index type explicitly as CLUSTERED or NONCLUSTERED. Omitting this can lead to unintended performance issues and lack of clarity about index behavior.

Example of index creation without explicit type specification:

SQL
Copy
1CREATE INDEX IX_Example ON TableName(ColumnName);

This query does not specify whether the index is clustered or nonclustered. Such omissions can lead to default configurations that might not align with the database’s performance needs or design intentions.

  • Default index type decisions might not suit the specific performance requirements of your database.

  • Ambiguity about the index type can complicate maintenance and troubleshooting.

How to fix

Explicitly specify the index type as either CLUSTERED or NONCLUSTERED when creating an index to ensure clarity and optimize performance.

Follow these steps to address the issue:

  1. Review the current index creation syntax to identify any indexes created without an explicit index type specification.

  2. Determine the appropriate index type based on your database design and performance requirements. Consider using CLUSTERED for indexes that should define the physical storage order of the data, and NONCLUSTERED for secondary indexes.

  3. Modify the index creation statement to include the chosen index type. This involves explicitly specifying either CLUSTERED or NONCLUSTERED in the CREATE INDEX statement.

Example of index creation with explicit type specification:

SQL
Copy
1CREATE NONCLUSTERED INDEX IX_Example ON TableName(ColumnName);

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
2 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
 2    ON Sales.SalesPerson (SalesQuota, SalesYTD);
 3
 4-- NONCLUSTERED or CLUSTERED type should be specified
 5CREATE INDEX IX_ProductVendor_VendorID 
 6    ON Purchasing.ProductVendor (VendorID); 
 7
 8-- NONCLUSTERED or CLUSTERED type should be specified
 9CREATE UNIQUE INDEX AK_UnitMeasure_Name 
10    ON Production.UnitMeasure(Name);
11
12-- NONCLUSTERED or CLUSTERED type should be specified
13CREATE UNIQUE INDEX AK_Index ON #Test (C2)
14    WITH (IGNORE_DUP_KEY = ON);
Analysis Results
  Message Line Column
1 SA0022 : Index type (CLUSTERED or NONCLUSTERED) not specified. 5 7
2 SA0022 : Index type (CLUSTERED or NONCLUSTERED) not specified. 9 14
3 SA0022 : Index type (CLUSTERED or NONCLUSTERED) not specified. 13 14
See Also

Other Resources

Analysis Rules