SA0040 : Consider moving the column reference to one side of the comparison operator in order to use the column index

Placing a column reference on one side of a comparison operator allows the query optimizer to leverage the column’s index effectively and can improve query performance.

Description

Comparing a constant with a column directly within search conditions can hinder performance. This is because such comparisons may prevent the SQL Server optimizer from using available column indexes effectively.

Example of problematic query where a constant is compared to a column:

SQL
1WHERE ColumnName + 10 >  100;

This approach might trigger a table scan instead of leveraging an index on ColumnName, thus affecting query efficiency.

  • Leads to unnecessary full table scans, increasing resource usage and query execution time.

  • Reduces effectiveness of composite indexes if the compared column is not the leftmost in the index.

How to fix

Improve query performance by restructuring comparisons to utilize column indexes effectively.

Follow these steps to address the issue:

  1. Identify the constant-to-column comparison in the SQL query, such as ColumnName + 10 = 100.

  2. Reformulate the comparison to place the column on the one side of the operator, like ColumnName = 110.

  3. Ensure that the column used in the comparison is indexed for optimal query performance.

Example of corrected query:

SQL
1WHERE ColumnName >  90;

Scope

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

Parameters
Name Description Default Value
IndexedColumnsOnly

Only indexed columns will result rule violaiton. Column which is part of composite index, will be considered indexed only if is the leftmost column in index.

yes

Remarks

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

Effort To Fix
13 minutes per issue.
Categories

Performance Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
1SELECT  b.[ProductAssemblyID]
2      , b.[ComponentID]
3FROM  [Production].[BillOfMaterials] AS b
4WHERE  b.[ProductAssemblyID] + 10 > 1200

Analysis Results
  Message Line Column
1 SA0040 : Consider moving the column [Production].[BillOfMaterials].[ProductAssemblyID] reference to one side of the comparison operator in order to use the column index. 4 9
See Also

Other Resources