Click or drag to resize

SA0028 : Function call can be extracted from the WHERE clause to avoid unnecessary table scan

The topic describes the SA0028 analysis rule.

Message

Function call can be extracted from the WHERE clause to avoid unnecessary table scan

Description

Consider extracting function calls made in the WHERE clause into local variable, if the function result does not depend on table columns and if it can be evaluated before executing the query. This is necessary, because such function calls can sometimes make the SQL optimizer to perform unnecessary table scan.

Note Note

The rule is deprecated is found to be not valid in the new SQL Server versions. The index scan is not generated in the execution plan when there is no object or column passed to the function as parameter.

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

IgnoreFunctionsList

Comma separated lists of functions which to be ignored.

GetDate

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Performance Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1-- The call to the CHECKSUM function can be avoided by setting its result to a variable which later to be used in the WHERE clause
 2SELECT     *
 3FROM       Production.Product
 4WHERE      CHECKSUM( N'Bearing Ball' ) = cs_Pname
 5       AND Name = N'Bearing Ball';
 6
 7DECLARE @Checksum AS INT
 8
 9SET @Checksum = CHECKSUM( N'Bearing Ball' )
10
11SELECT     *
12FROM       Production.Product
13WHERE      @Checksum = cs_Pname
14       AND Name = N'Bearing Ball';
15
16
17-- Filtering column wrapped inside a function is used in the WHERE clause
18SELECT *
19FROM users
20WHERE substring( firstname,1,1)='m'
21
22SELECT *
23FROM users
24WHERE isnull( status, 0)>0
25
26-- CAST is ignored
27SELECT *
28FROM users
29WHERE cast(status AS int)>0
30
31SELECT *
32FROM users
33WHERE CONVERT(  status,
34               111)>0
35
36SELECT *
37FROM users
38WHERE COALESCE( status, 2, 0, 1)>0
39
40
41-- Filtering column wrapped inside a function is used in the WHERE clause
42SELECT *
43FROM users
44WHERE firstname LIKE 'm%'
45
46SELECT OrderId,
47       ProfileId,
48       OrderNumber,
49       Created
50FROM Orders
51WHERE fnGetPreviousDate()>getdate() - 1
52        OR DATEADD(day,2,Created) > getDate()
53
54-- Filtering column wrapped inside a function is used in the WHERE clause
55SELECT OrderId,
56       OrderNumber,
57       Created
58FROM Orders
59WHERE fnGetDate(getdate()) >'2010/09/01' 
60        OR DATEADD(day,2,getdate()) > getDate()
61
62DECLARE @Date datetime
63
64SET @Date = GetDate()
65
66SELECT * FROM Orders WHERE fnGetDate(@Date) >'2010/09/01' 
67
68select Time from Times 
69where time >  dateadd(DD, -2, GETDATE());
70
71select Time from Times 
72where time >  year(GETDATE());
73
74declare @today datetime = getdate();
75
76select Time from Times 
77where time >  dateadd(DD, -2, @today);
78
79select Time from Times 
80where time >  year(@today);

Analysis Results

 MessageLineColumn
1SA0028 : Function call can be extracted from the WHERE clause to avoid unnecessary table scan.296
2SA0028 : Function call can be extracted from the WHERE clause to avoid unnecessary table scan.336
3SA0028 : Function call can be extracted from the WHERE clause to avoid unnecessary table scan.516
4SA0028 : Function call can be extracted from the WHERE clause to avoid unnecessary table scan.596
5SA0028 : Function call can be extracted from the WHERE clause to avoid unnecessary table scan.604
6SA0028 : Function call can be extracted from the WHERE clause to avoid unnecessary table scan.6627
7SA0028 : Function call can be extracted from the WHERE clause to avoid unnecessary table scan.6914
8SA0028 : Function call can be extracted from the WHERE clause to avoid unnecessary table scan.7214
9SA0028 : Function call can be extracted from the WHERE clause to avoid unnecessary table scan.7714
10SA0028 : Function call can be extracted from the WHERE clause to avoid unnecessary table scan.8014
See Also

Other Resources