SA0027 : Avoid wrapping filtering columns within a function in the WHERE clause
Introduction
Section titled “Introduction”Avoid wrapping columns in functions within the WHERE clause to ensure efficient index usage.
Description
Section titled “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:
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.
How to fix
Section titled “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:
SELECT * FROM EmployeesWHERE HireDate >= '2023-01-01' AND HireDate < '2024-01-01';The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “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
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”1 hour per issue.
Categories
Section titled “Categories”Performance Rules, Bugs
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL”-- Filtering column wrapped inside a function is used in the WHERE clauseSELECT *FROM usersWHERE substring( firstname,1,1)='m'
SELECT *FROM usersWHERE isnull( status, 0)>0
-- CAST is ignoredSELECT *FROM usersWHERE cast(status AS int)>0
SELECT *FROM usersWHERE CONVERT( status, 111)>0
SELECT *FROM usersWHERE COALESCE( status, 2, 0, 1)>0
-- Filtering column wrapped inside a function is used in the WHERE clauseSELECT *FROM usersWHERE firstname LIKE 'm%'
SELECT OrderId, ProfileId, OrderNumber, CreatedFROM OrdersWHERE dbo.fnGetDate(Created)>getdate() - 1 OR DATEADD(day,2,Created) > getDate()
-- Filtering column wrapped inside a function is used in the WHERE clauseSELECT OrderId, OrderNumber, CreatedFROM OrdersWHERE fnGetDate(Created)/*IGNORE:SA0027*/ >'2010/09/01' OR DATEADD(day,2,getdate()) > getDate()Analysis Results
Section titled “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 |