Export (0) Print
Expand All

SR0004: Avoid using columns that do not have indexes as test expressions in IN predicates

RuleId

SR0004

Category

Microsoft.Performance

Breaking Change

Non-breaking

An IN predicate references a column that does not have an index.

You cause a table scan if you use a WHERE clause that references one or more columns that are not indexed as part of an IN predicate. The table scan will reduce performance.

To resolve this issue, you must make one of the following changes:

  • Change the IN predicate to reference only those columns that have an index.

  • Add an index to any column that the IN predicate references and that does not already have an index.

You might suppress this warning if the table will never contain more than a few rows.

In this example, a simple SELECT statement references a column, [c1], that did not have an index. The second statement defines an index that you can add to resolve this warning.

CREATE PROCEDURE [dbo].[Procedure3WithWarnings]
AS
SELECT [Comment] 
FROM [dbo].[Table2]  
WHERE [c1] IN (1, 2, 3)

CREATE INDEX [IX_Table2_C1]
ON [dbo].[Table2] (c1);

Community Additions

ADD
Show:
© 2014 Microsoft