Click or drag to resize

SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause

The topic describes the SA0127 analysis rule.

Message

Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause

Description

The rule checks the query filtering clauses for function call which has filtering columns as parameter values.

When a filtering 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.

Note Note

The rule is similar to SA0027, but reports only when system functions are found.

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

IgnoreFunctionsList

Comma separated lists of functions which to be ignored.

-

IngoreUserDefinedFunctions

The parameter specifies if the user defined functions to be ignored by the rule.

yes

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
 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-- Filtering column wrapped inside an user defined function is used in the WHERE clause
29SELECT *
30FROM users
31WHERE firstname LIKE 'm%'
32
33SELECT OrderId,
34       ProfileId,
35       OrderNumber,
36       Created
37FROM Orders
38WHERE dbo.fnGetDate(Created)>getdate() - 1
39        OR DATEADD(day,2,Created) > getDate()
40
41-- Filtering column wrapped inside a built in function is used in the WHERE clause
42SELECT OrderId,
43       OrderNumber,
44       Created
45FROM Orders
46WHERE fnBuiltInGetDate(Created)/*IGNORE:SA0127*/ >'2010/09/01' 
47        OR DATEADD(day,2,getdate()) > getDate()
48
49SELECT *
50FROM users
51WHERE substring( firstname,1,1)='m' /*IGNORE:SA0127(LINE)*/
52
53SELECT * 
54FROM Table_1 t1
55JOIN Table_2 t2 ON LTRIM(t1.testdata1) = SUBSTRING(t2.testdata1,1,1);

Analysis Results

 MessageLineColumn
1SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause.56
2SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause.96
3SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause.156
4SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause.246
5SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause.404
6SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause.5619
7SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause.5641
See Also

Other Resources