Click or drag to resize

SA0040 : Consider moving the column reference to one side of the comparison operator in order to use the column index

The topic describes the SA0040 analysis rule.

Message

Consider moving the column reference to one side of the comparison operator in order to use the column index

Description

The rule checks for constant to column comparison expressions inside search conditions. Try to rewrite the comparison expression and move the column on the one side of the comparison operator. This will allow the query optimizer to take advantage of the column index (assuming one is available) instead of performing table scan.

Note Note

When column is part of composite index, it is considered indexed for the IndexedColumnsOnly parameter, only if the column is the leftmost column in index.

Scope

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

Parameters
NameDescriptionDefault Value
IndexedColumnsOnly

Only indexed columns will result rule violaiton. Column which is part of composite index, will be considered indexed only if is the leftmost column in index.

yes

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:SA0040

Remarks

The rule requires SQL Connection. If there is no connection provided, 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
 4
 5SELECT   b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
 6FROM [Production].[BillOfMaterials] b
 7WHERE ( b.[ComponentID] + 5 > @param1)
 8
 9
10SELECT  b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
11FROM [Production].[BillOfMaterials] b
12WHERE ( b.[ComponentID] > (@param1 - 5))
13
14DECLARE  @StartProductID [int],
15    @CheckDate [datetime]
16
17BEGIN
18    SET NOCOUNT ON;
19
20    --Use recursive query to generate a multi-level Bill of Material (i.e. all level 1 components of a level 0 assembly, all level 2 components of a level 1 assembly)
21    WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns
22    AS (
23        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly
24        FROM [Production].[BillOfMaterials] b
25            INNER JOIN [Production].[Product] p 
26            ON b.[ProductAssemblyID] = p.[ProductID] 
27        WHERE b.[ComponentID] = @StartProductID 
28            AND @CheckDate >= b.[StartDate] -1
29            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
30                        and p.[ProductID]  + 1 = 1234
31        UNION ALL
32        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor
33        FROM [BOM_cte] cte
34            INNER JOIN [Production].[BillOfMaterials] b 
35            ON cte.[ProductAssemblyID] = b.[ComponentID]
36            INNER JOIN [Production].[Product] p 
37            ON b.[ProductAssemblyID] + 10 > 1200
38        WHERE @CheckDate >= b.[StartDate] + 1
39            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
40        )
41    -- Outer select from the CTE
42    SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
43    FROM [BOM_cte] b
44
45    GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
46    ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
47    OPTION (MAXRECURSION 25) 
48
49SELECT * FROM [Production].[Product] p WHERE p.[ProductID]  /*IGNORE:SA0040*/ + 1 = 1234 AND p.[PerAssemblyQty] < 123
50
51END

Analysis Results

 MessageLineColumn
1SA0040 : Consider moving the column [Production].[Product].[ProductID] reference to one side of the comparison operator in order to use the column index.309
2SA0040 : Consider moving the column [Production].[BillOfMaterials].[ProductAssemblyID] reference to one side of the comparison operator in order to use the column index.3717
See Also

Other Resources