Skip to content

SA0028 : Function call can be extracted from the WHERE clause to avoid unnecessary table scan

Minimize unnecessary function calls in the WHERE clause to improve query performance.

The WHERE clause is crucial for filtering results and optimizing query performance. However, using function calls that do not depend on table columns within the WHERE clause can lead to inefficiencies. Such function calls might force the SQL Server optimizer to conduct unnecessary table scans, impacting performance negatively.

Example of a query with a problematic function call in the WHERE clause:

SELECT * FROM Employees WHERE YEAR(GETDATE()) = YearHired;

In this example, the function YEAR(GETDATE()) is invariant-it does not rely on any data from the database tables. By placing it in the WHERE clause, the optimizer may execute this function repeatedly for each row, increasing execution time, especially with large datasets. A better approach would be extracting this function call into a variable before executing the query, thus reducing overhead.

  • Decreased query performance due to repeated function execution.

  • Increased likelihood of unnecessary table scans by the SQL optimizer.

Optimize T-SQL queries by minimizing unnecessary function calls in the WHERE clause to enhance performance and prevent inefficient index usage.

Follow these steps to address the issue:

1.Identify function calls within the WHERE clause that do not depend on any table columns, such as YEAR(GETDATE()) .

2.Move these invariant function calls out of the WHERE clause by calculating their results once and storing them in variables or constants.

3.Use these variables in the WHERE clause to compare against table columns, which will reduce repetitive function execution.

For example:

-- Before optimization
SELECT * FROM Employees WHERE YEAR(GETDATE()) = YearHired;
-- After optimization
DECLARE @CurrentYear INT;
SET @CurrentYear = YEAR(GETDATE());
SELECT * FROM Employees WHERE @CurrentYear = YearHired;

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

The rule does not need Analysis Context or SQL Connection.

5 minutes per issue.

Performance Rules, Bugs

There is no additional info for this rule.

-- The call to the CHECKSUM function can be avoided by setting its result to a variable which later to be used in the WHERE clause
SELECT *
FROM Production.Product
WHERE CHECKSUM( N'Bearing Ball' ) = cs_Pname
AND Name = N'Bearing Ball';
DECLARE @Checksum AS INT
SET @Checksum = CHECKSUM( N'Bearing Ball' )
SELECT *
FROM Production.Product
WHERE @Checksum = cs_Pname
AND Name = N'Bearing Ball';

No violations found.

Analysis Rules