SR0004: Avoid using columns that do not have indexes as test expressions in IN predicates
RuleId |
SR0004 |
Category |
Microsoft.Performance |
Breaking Change |
Non-breaking |
Cause
An IN predicate references a column that does not have an index.
Rule Description
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.
How to Fix Violations
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.
When to Suppress Warnings
You might suppress this warning if the table will never contain more than a few rows.
Example
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);