Use Function Results

Built-in functions with literal constant inputs are simplified during optimization to resultant constant values. However, user defined functions or built-in functions with variable[1] inputs are only simplified to resultant constants for the purposes of plan optimization for the following functions:

 

Lower

Upper

RTrim

Datediff

Dateadd

Datepart

Substring

Charindex

Length

SUser_SName

IsNull

GetDate

GetUTCDate

 

With other functions, the optimizer will guess the selectivity of predicates and this may lead to a poorly performing plan. It is better for the query writer to set the value of the scalar expression to a variable and pass this value to the SQL statement as a parameter to a stored procedure or parameter to sp_executesql. The query optimizer can determine the value contained within a parameter and will use this value to determine the best plan. In this way, the query optimizer will be able to optimize with the resultant expression value.

If the expression is present in the SQL query, the optimizer will not know the resultant value and will guess the selectivity of predicates containing this expression. This can lead to poorly performing plans. Note that column inputs to expressions must be present in the query statement since they require query context to have meaning.

---

[1] Corelated parameter, local variable, or procedure parameter.