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

Introduction

System functions in filtering clauses can obscure column usage, leading to inefficient query performance.

Description

Wrapping columns in system functions within filtering clauses can cause performance issues because the query optimizer cannot effectively utilize indexes. This oversight can result in slower query execution times, especially in large datasets or high-performance environments.

For example:

-- Example of problematic query using system function in a filtering clause
SELECT * FROM Employees WHERE YEAR(HireDate) = 2023;

This query is problematic because using the YEAR function on the HireDate column prevents the optimizer from using any available indexes. Instead of scanning through only relevant rows, the database system might need to examine every row, leading to inefficient query processing.

Performance degradation due to full table scans instead of index seeks.
Increased resource usage, such as CPU and memory, which could impact other operations.

How to fix

Avoid wrapping columns in system functions within filtering clauses to enhance query performance by allowing the query optimizer to effectively utilize indexes.

Follow these steps to address the issue:

1.Refactor the query to eliminate the use of system functions on columns within filtering clauses.

2.Rewrite the condition to compare columns directly against constant values or variables. For example, instead of using a function directly on the column, calculate equivalent values beforehand.

3.Test the modified query to ensure it performs better and returns the correct results.

For example:

-- Example of corrected query
SELECT * FROM Employees WHERE 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,DatePart,DateName,DateDiff,DateAdd
IgnoreUserDefinedFunctions	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 Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix

1 hour per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

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

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

SELECT  *
FROM  Orders
WHERE  dbo.fnGetDate( Created ) > GETDATE() - 1 OR
       DATEADD( day, 2, Created ) > GETDATE()

Analysis Results

	Message	Line	Column
1	SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause.	5	7
2	SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause.	13	7
3	SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause.	17	7
4	SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause.	21	7

Analysis Rules