Some SQL functions are constants. They get evaluated once at the beginning of the query and remain the same all over the execution time. One good example is the GETDATE() function.
Unlike user defined functions, there are some system functions that are evaluated once only and the result is used all over the execution time. Consider the query below:
The output resultset will repeat one single date value across all customers. GETDATE()
said to be a Runtime Constant
whose value is evaluated once only.
Yes, it IS Runtime Constant. Then what???!!
There is a kind of query performance concern some developers have regarding the number of times a function or an operation runs in a query. They are
afraid that if SQL executes functions multiple times, the query might go slow if data gets bigger. If you find the function as a Runtime Constant, don't worry
about the execution performance. Runtime Constants are executed once only.
How to figure out if it is a Runtime Constant or not?
To distinguish between Runtime Constants and others, you need to check the execution plan. Here is the execution plan of the above query:
The Compute Scalar operator in the execution plan above represents the GETDATE()
To check if it is a Runtime Constant or not, you need to check the properties of this operator. Here is the properties:
in the Properties of the operator means that it is a Runtime Constant. If it is not a Runtime Constant, it will start with
What are other Runtime Constant?
There are times that a variable, not a column, is cast to other types. As long as one single variable is cast, it happens to be a Runtime Constant.
The graphical execution plan in this case does not help much.
Even the Properties Window of the two operators in the execution plan does not imply any kind of Expr
However, the XML version of the execution plan will provide all data available. Right-click on the execution plan and choose Show Execution Plan XML
then search for ConstExpr
. You will notice that the execution plan XML provides all details that might heppen to be hidden in the graphical version.
That's it. The cast has been evaluated once only in the Clustered Index Scan predicate; it is a Runtime Constant.
Any other example?
The number of Runtime Constant is not that many. Some other ons include:
- Implicit conversions that SQL Server does on variables
- Use of DB_ID() function
- Use of ServerProperty() function
NEWID() is not a Runtime Constant
The NEWID() function is not a Runtime Constant and is evaluated for every single row.