Click or drag to resize

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.

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.

Categories

Performance Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 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

 MessageLineColumn
1SA0055 : Consider indexing the referenced by the IN predicate column [SpecialOffer].[Category] in order to avoid a table scan.126
2SA0055 : Consider indexing the referenced by the IN predicate column [SpecialOffer].[Category] in order to avoid a table scan.185
See Also

Other Resources