Incorrect usage of const UDF

It is not recommended to use a UDF that returns a const value in JOIN or WHERE clauses or in a select list – it is better to store the UDF value in a variable.

Available in

 SQL Monitor

SQL Prompt

 SQL Code Guard

Developers tend to expect to be able to set global values in a database to provide constants such as the value of Pi, or variables such as the tax rate, language, file URNs or URLs. User-defined scalar functions return a single value, and so seem to provide the ideal way of doing so. This is fine for functions that are executed infrequently, and process relatively small data sets, but in other cases it can cause dramatic query performance problems. There is an overhead in calling any SQL Server function that has a BEGIN…END block because, unless we allow SQL Server to verify its output, by creating the function using schema binding, it will re-execute the function for every row, before data can be filtered, even though it is obvious to you that it will return the same value every time. Unless you are confident with schema binding, and its consequences when making database changes, it is better to use either transfer the value to a variable, or use a module such as a view or inline Table-value function.

See also: Misuse of the scalar user-defined function as a constant (PE017)