SR0006: Move a column reference to one side of a comparison operator to use a column index
RuleId |
SR0006 |
Category |
Microsoft.Performance |
Breaking Change |
Non-breaking |
Cause
As part of a comparison, an expression contains a column reference.
Rule Description
Your code could cause a table scan if it compares an expression that contains a column reference.
How to Fix Violations
To resolve this issue, you must rework the comparison so that the column reference appears alone on one side of the comparison operator, instead of inside an expression. When you run the code that has the column reference alone on one side of the comparison operator, SQL Server can use the column index, and no table scan is performed.
When to Suppress Warnings
You might suppress this warning if the table whose column is being compared will never contain more than a few rows.
Example
In the first procedure, a WHERE clause includes column [c1] in an expression as part of a comparison. In the second procedure, the comparison results will be identical but never require a table scan.
CREATE PROCEDURE [dbo].[Procedure3WithWarnings]
@param1 int
AS
SELECT [c1], [c2], [c3], [Comment]
FROM [dbo].[Table2]
WHERE ([c1] + 5 > @param1)
CREATE PROCEDURE [dbo].[Procedure3Fixed]
@param1 int
AS
SELECT [c1], [c2], [c3], [Comment]
FROM [dbo].[Table2]
WHERE ([c1] > (@param1 - 5))