This weblog is intended to help T-SQL developers get the best performing query plans from SQL Server. Some of the posts describe simple mistakes that can be easily avoided. Other posts describe complex solutions to limitations in SQL Server. Lastly, some posts describe recent improvements in SQL Server, and the means to employ them best.
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 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.
Anonymous comments are disabled
