SA0027 : Avoid wrapping filtering columns within a function in the WHERE clause

The topic describes the SA0027 analysis rule.

Message

Avoid wrapping filtering columns within a function in the WHERE clause

Description

When a filtering WHERE clause column is wrapped inside a function, the query optimizer does not see the column and if an index exists on the column, the index most likely will not to be used.

Scope

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

Parameters
Name Description Default Value
IgnoreFunctionsList

Comma separated lists of functions which to be ignored.

IsNull,GetDate,DatePart,DateName,DateDiff,DateAdd

IgnoreUserDefinedFunctions

The parameter specifies if the user defined functions to be reported when they are wrapping a filtering columns.

no

IgnoreBuiltInFunctions

The parameter specifies if the built-in functions functions to be reported when they are wrapping a filtering columns.

no

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Performance Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1-- Filtering column wrapped inside a function is used in the WHERE clause
 2SELECT *
 3FROM users
 4WHERE substring( firstname,1,1)='m'
 5
 6SELECT *
 7FROM users
 8WHERE isnull( status,
 9              0)>0
10
11-- CAST is ignored
12SELECT *
13FROM users
14WHERE cast(status AS int)>0
15
16SELECT *
17FROM users
18WHERE CONVERT(  status,
19               111)>0
20
21SELECT *
22FROM users
23WHERE COALESCE( status,
24                2,
25                0,
26                1)>0
27
28
29-- Filtering column wrapped inside a function is used in the WHERE clause
30SELECT *
31FROM users
32WHERE firstname LIKE 'm%'
33
34SELECT OrderId,
35       ProfileId,
36       OrderNumber,
37       Created
38FROM Orders
39WHERE dbo.fnGetDate(Created)>getdate() - 1
40        OR DATEADD(day,2,Created) > getDate()
41
42-- Filtering column wrapped inside a function is used in the WHERE clause
43SELECT OrderId,
44       OrderNumber,
45       Created
46FROM Orders
47WHERE fnGetDate(Created)/*IGNORE:SA0027*/ >'2010/09/01' 
48        OR DATEADD(day,2,getdate()) > getDate()

Analysis Results

  Message Line Column
1 SA0027 : Avoid wrapping filtering columns within a function in the WHERE clause. 5 6
2 SA0027 : Avoid wrapping filtering columns within a function in the WHERE clause. 15 6
3 SA0027 : Avoid wrapping filtering columns within a function in the WHERE clause. 24 6
4 SA0027 : Avoid wrapping filtering columns within a function in the WHERE clause. 40 10
See Also

Other Resources