Click or drag to resize

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

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

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

 MessageLineColumn
1SA0027 : Avoid wrapping filtering columns within a function in the WHERE clause.56
2SA0027 : Avoid wrapping filtering columns within a function in the WHERE clause.156
3SA0027 : Avoid wrapping filtering columns within a function in the WHERE clause.246
4SA0027 : Avoid wrapping filtering columns within a function in the WHERE clause.4010
See Also

Other Resources