Click or drag to resize

SA0047 : Consider indexing the column as it is used in a WHERE clause or JOIN condition

The topic describes the SA0047 analysis rule.

Message

Consider indexing the column as it is used in a WHERE clause or JOIN condition

Description

The rules checks WHERE or JOIN clauses for non-indexed columns staying on the one side of comparison expression. Indexing those columns may improve the query performance.

Scope

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

Parameters
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0047

MinimumParentTableRowCount

The parameter specifies the minimum number of rows that the parent table of the indexed column must have for the rule to alert. This way small tables can be ignored.

1000

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
 1CREATE PROCEDURE [dbo].[Procedure3WithWarnings]
 2@param1 int
 3AS
 4SELECT   *
 5FROM [Production].[BillOfMaterials] b
 6WHERE ( b.[ComponentID] + 5 > @param1) or  b.[BOMLevel] > 5
 7
 8SELECT  *
 9FROM [Production].[BillOfMaterials] b
10WHERE ( b.[ComponentID] > (@param1 - 5)) or  b.[BOMLevel] = @param1
11
12SELECT  *
13FROM [Production].[BillOfMaterials] b
14WHERE ( b.[ComponentID] > (@param1 - 5)) or  b.[BOMLevel] /*IGNORE:SA0047*/ = @param1

Analysis Results

 MessageLineColumn
1SA0047 : Consider indexing column [Production].[BillOfMaterials].[BOMLevel] as it is referenced in a WHERE clause or JOIN condition.645
2SA0047 : Consider indexing column [Production].[BillOfMaterials].[BOMLevel] as it is referenced in a WHERE clause or JOIN condition.1047
See Also

Other Resources