Export (0) Print
Expand All

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

As part of a comparison, an expression contains a column reference.

Your code could cause a table scan if it compares an expression that contains a column reference.

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.

You might suppress this warning if the table whose column is being compared will never contain more than a few rows.

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

Community Additions

ADD
Show:
© 2015 Microsoft