SA0135 : Found filtering columns wrapped inside User-Defined Function call

The SQL Server query optimizer may not utilize indexes on columns in filtering clauses wrapped inside user-defined functions, even if these indexes exist.

For example:

1-- Example of problematic query with a user-defined function
2SELECT * FROM Employees WHERE dbo.GetStatus(EmployeeType) = 'Active';

In this example, the function dbo.GetStatus wraps the EmployeeType column. This wrapping prevents the query optimizer from recognizing the column directly, leading to potential inefficiencies, as it cannot effectively use any existing indexes on EmployeeType.

• Query performance may degrade due to full table scans instead of using indexes, increasing execution time.

• Increased resource consumption as a result of ineffective query plans generated by the optimizer.

Optimize the query to improve performance by ensuring the SQL Server query optimizer can effectively utilize indexes.

Follow these steps to address the issue:

1. Review the query execution plan to identify any performance bottlenecks caused by user-defined functions in filtering clauses.

2. Refactor the query to eliminate user-defined functions from the filtering clauses. Instead, use a computed column or inline logic that SQL Server can optimize more effectively.

3. Ensure that indexes are available on columns used in filtering clauses to facilitate efficient query execution.

For example:

1-- Example of refactored query without a user-defined function
2SELECT * FROM Employees WHERE EmployeeType = 'Active';

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

The rule does not need Analysis Context or SQL Connection.

Performance Rules, Bugs

There is no additional info for this rule.

Other Resources