SA0055 : Consider indexing the columns referenced by IN predicates in order to avoid table scans

Introduction

Avoid using non-indexed columns in IN predicates to prevent performance issues in SQL queries.

Description

When you use an IN predicate in a query, the database engine needs to check if each row’s column value is contained within a specific set of values. If this column is non-indexed, the query can lead to inefficient table scans instead of faster index seeks, significantly impacting query performance in SQL Server.

For example:

-- Example of problematic query
SELECT * FROM Employees WHERE DepartmentID IN (1, 2, 3);

In the above example, if the DepartmentID column does not have an index, SQL Server will perform a table scan to determine which rows match the criteria, potentially leading to slow query execution times.

Increased CPU and IO usage due to full table scans.
Slower query performance, impacting user experience or application response time.

How to fix

Improve query performance by ensuring columns in IN predicates are indexed to avoid full table scans.

Follow these steps to address the issue:

1.Identify the column used in the IN predicate within your query. For instance, in WHERE DepartmentID IN (1, 2, 3) , the column is DepartmentID .

2.Check if this column already has an index. You can do this using SQL Server Management Studio (SSMS) or by querying system views such as sys.indexes and sys.index_columns .

3.If the column is not indexed, create an index on it to optimize query performance. Use the following template to add an index:

For example:

CREATE INDEX IX_DepartmentID ON Employees(DepartmentID);

Re-run the query and verify improved performance by observing execution time and resource usage.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix

1 hour per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

-- NOT IN predicae is igonred as it will result a scan operator.
SELECT [Comment]
FROM [Sales].[SpecialOffer]
WHERE [SpecialOfferID] IN (1, 2, 3)
AND  [Category] NOT IN ('Category1', 'Category2','Category3' )


-- Category column have to be indexed in order to avoid a table scan during query processing.
SELECT [Comment]
FROM [Sales].[SpecialOffer]   a
WHERE [Category] IN (Description)

-- -- Category column have to be indexed in order to avoid a table scan during query processing.
SELECT [Comment]
FROM [Sales].[SpecialOffer]
WHERE [SpecialOfferID] IN (1, 2, 3)
AND  [Category] IN ('Category1', 'Category2','Category3' )

-- If the table and columns cannot be resolved in the current connection context, the rule will be suppressed.
SELECT [Comment]
FROM [dbo].[Table2]
WHERE [c1] IN (1, 2, 3)

Analysis Results

	Message	Line	Column
1	SA0055 : Consider indexing the referenced by the IN predicate column [SpecialOffer].[Category] in order to avoid a table scan.	12	6
2	SA0055 : Consider indexing the referenced by the IN predicate column [SpecialOffer].[Category] in order to avoid a table scan.	18	5

Analysis Rules