Share via


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))

See Also

Concepts

Analyzing Database Code to Improve Code Quality