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

The topic describes the SA0055 analysis rule.

Message

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

Description

The rule checks for IN predicates that reference non indexed columns.

Using columns which do not have index can cause a performance reducing table scan.

How to fix

The following changes will help to avoid this issue:

– Add an index to the column referenced by the IN predicate.

– Change the IN predicate to reference only indexed columns.

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.
Categories

Performance Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1-- NOT IN predicae is igonred as it will result a scan operator.
 2SELECT [Comment] 
 3FROM [Sales].[SpecialOffer] 
 4WHERE [SpecialOfferID] IN (1, 2, 3)
 5AND  [Category] NOT IN ('Category1', 'Category2','Category3' )
 6
 7
 8-- Category column have to be indexed in order to avoid a table scan during query processing.
 9SELECT [Comment] 
10FROM [Sales].[SpecialOffer]   a
11WHERE [Category] IN (Description)
12
13-- -- Category column have to be indexed in order to avoid a table scan during query processing.
14SELECT [Comment] 
15FROM [Sales].[SpecialOffer] 
16WHERE [SpecialOfferID] IN (1, 2, 3)
17AND  [Category] IN ('Category1', 'Category2','Category3' )
18
19-- If the table and columns cannot be resolved in the current connection context, the rule will be suppressed.
20SELECT [Comment]  
21FROM [dbo].[Table2]  
22WHERE [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
See Also

Other Resources

Analysis Rules