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

Review the query and the and try to avoid wrapping of filtering columns

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
IngoreUserDefinedFunctions

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

yes
IgnoreColumnsFromTempTables

Ignore not equal comparison of columns of a temporary table or table variable.

yes
Remarks

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

Effort To Fix
1 hour per issue.
Categories

Performance Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 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
  Message Line Column
1 SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause. 5 6
2 SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause. 15 6
3 SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause. 19 6
4 SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause. 24 6
5 SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause. 56 19
6 SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause. 56 41
See Also

Other Resources

Analysis Rules