SA0022 : Index type (CLUSTERED or NONCLUSTERED) not specified

Introduction

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:

CREATE 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:

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

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
    ON Sales.SalesPerson (SalesQuota, SalesYTD);

-- NONCLUSTERED or CLUSTERED type should be specified
CREATE INDEX IX_ProductVendor_VendorID
    ON Purchasing.ProductVendor (VendorID);

-- NONCLUSTERED or CLUSTERED type should be specified
CREATE UNIQUE INDEX AK_UnitMeasure_Name
    ON Production.UnitMeasure(Name);

-- NONCLUSTERED or CLUSTERED type should be specified
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    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

Analysis Rules