Skip to content

SA0238 : The user-defined function appearing in the query filter can cause performance problems

Ensure user-defined functions in WHERE clause or JOIN conditions reference table columns effectively.

When using user-defined functions in the WHERE clause or JOIN conditions, a common problem arises if these functions return a deterministic or constant result and do not depend on table columns. Such functions can be evaluated before the query executes. However, if they are not schema-bound, SQL Server evaluates them for every row, leading to performance inefficiencies.

For example:

-- Example of inefficient use in a WHERE clause
SELECT * FROM Users WHERE SomeFunction() = 1;

In this example, SomeFunction() doesn’t depend on any columns from the Users table. If it’s not schema-bound and is used within a query, SQL Server may evaluate it for each row, which is unnecessary and slows down query execution.

  • Increased execution time as the function is evaluated row by row.

  • Potential bottleneck due to unnecessary computational overhead.

Optimize performance by extracting function calls from the WHERE clause and assigning them to local variables.

Follow these steps to address the issue:

1.Identify user-defined function calls in the WHERE clause or JOIN conditions that do not depend on table columns.

2.Declare a local variable to store the function’s result outside the query using a DECLARE statement. This way, the function is evaluated only once.

3.Assign the result of the function call to the local variable before executing the query.

4.Modify the original query to replace the function call in the WHERE clause or JOIN condition with the local variable.

For example:

-- Example of corrected query
DECLARE @Result INT;
SET @Result = SomeFunction();
SELECT * FROM Users WHERE @Result = 1;

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

Rule has no parameters.

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

1 hour per issue.

Performance Rules, Bugs

There is no additional info for this rule.

SELECT OrderId,
ProfileId,
OrderNumber,
Created
FROM Orders o
WHERE fnGetPreviousDate() > Created
SELECT OrderId,
OrderNumber,
Created
FROM Orders
WHERE fnGetDate(getdate()) > Created
OR DATEADD(day,2,getdate()) > getDate()
DECLARE @Date datetime
SET @Date = GetDate()
SELECT * FROM dbo.Orders WHERE fnGetDate(@Date) > Created
select * from Production.Product where '00000' + ProductNumber = ufnLeadingZeros(12)
select * from Production.Product where '00000' + ProductNumber = dbo.ufnLeadingZeros(12)
select * from Purchasing.PurchaseOrderHeader where dbo.ISOweek1(OrderDate) = dbo.ISOweek1(getdate())
  Message Line Column
1 SA0238 : The user-defined function [fnGetPreviousDate] appearing in the query filter can cause performance problems. 6 6
2 SA0238 : The user-defined function [fnGetDate] appearing in the query filter can cause performance problems. 12 6
3 SA0238 : The user-defined function [fnGetDate] appearing in the query filter can cause performance problems. 18 31
4 SA0238 : The user-defined function [ISOweek1] appearing in the query filter can cause performance problems. 22 81

Analysis Rules