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.

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:
-
Identify the constant-to-column comparison in the SQL query, such as ColumnName + 10 = 100.
-
Reformulate the comparison to place the column on the one side of the operator, like ColumnName = 110.
-
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.

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 |

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.

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

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 |
