Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

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





Breaking Change


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]
SELECT [Comment] 
FROM [dbo].[Table2]  
WHERE [c1] IN (1, 2, 3)

ON [dbo].[Table2] (c1);

Community Additions

© 2015 Microsoft