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

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:

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.

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:

1WHERE ColumnName >  90;

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

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

Performance Rules, Code Smells

There is no additional info for this rule.

Other Resources