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

Avoid wrapping columns in functions within the WHERE clause to ensure efficient index usage.

Description

When a column within a WHERE clause is wrapped in a function, SQL Server’s query optimizer cannot effectively utilize indexes on that column. This may result in suboptimal query performance due to a full table scan instead of an index seek.

Example of problematic query:

SQL
Copy
1SELECT * FROM Employees WHERE YEAR(HireDate) = 2023;

In the above query, the function YEAR(HireDate) prevents the use of any existing index on the HireDate column. Such function-wrapped columns can lead to inefficient query plans, increasing execution time and resource consumption.

  • Decreased query performance due to full table scans instead of index seeks.

  • Higher CPU and I/O utilization, which can impact overall system performance.

How to fix

Avoid wrapping columns in functions within the WHERE clause to ensure efficient index usage and enhance query performance.

Follow these steps to address the issue:

  1. Analyze the query to identify any columns in the WHERE clause that are wrapped in functions. For instance, a query like SELECT * FROM Employees WHERE YEAR(HireDate) = 2023; wraps the HireDate column in a function.

  2. Refactor the query to avoid using functions on column references in the WHERE clause. Instead, use logical expressions that allow for index utilization. For the given example, you can rewrite the condition to compare date ranges directly.

  3. Check if the column has an appropriate index. If not, consider creating an index to optimize the query performance.

Refactored query to improve index usage:

SQL
Copy
1SELECT * FROM Employees 
2WHERE HireDate >= '2023-01-01' AND HireDate < '2024-01-01';

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.

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

Analysis Rules