Skip to content

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.

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:

SELECT * 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.

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:

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

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

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

The rule does not need Analysis Context or SQL Connection.

1 hour per issue.

Performance Rules, Bugs

There is no additional info for this rule.

-- Filtering column wrapped inside a function is used in the WHERE clause
SELECT *
FROM users
WHERE substring( firstname,1,1)='m'
SELECT *
FROM users
WHERE isnull( status,
0)>0
-- CAST is ignored
SELECT *
FROM users
WHERE cast(status AS int)>0
SELECT *
FROM users
WHERE CONVERT( status,
111)>0
SELECT *
FROM users
WHERE COALESCE( status,
2,
0,
1)>0
-- Filtering column wrapped inside a function is used in the WHERE clause
SELECT *
FROM users
WHERE firstname LIKE 'm%'
SELECT OrderId,
ProfileId,
OrderNumber,
Created
FROM Orders
WHERE dbo.fnGetDate(Created)>getdate() - 1
OR DATEADD(day,2,Created) > getDate()
-- Filtering column wrapped inside a function is used in the WHERE clause
SELECT OrderId,
OrderNumber,
Created
FROM Orders
WHERE fnGetDate(Created)/*IGNORE:SA0027*/ >'2010/09/01'
OR DATEADD(day,2,getdate()) > getDate()
  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

Analysis Rules