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.
How to fix

Review the query and move the column reference to one side of the comparison operator in order to use the column index.

Scope

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

Parameters
Name Description Default 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
Remarks

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Effort To Fix
13 minutes per issue.
Categories

Performance Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 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
  Message Line Column
1 SA0040 : Consider moving the column [Production].[Product].[ProductID] reference to one side of the comparison operator in order to use the column index. 30 9
2 SA0040 : Consider moving the column [Production].[BillOfMaterials].[ProductAssemblyID] reference to one side of the comparison operator in order to use the column index. 37 17
See Also

Other Resources

Analysis Rules