Some SQL functions are constants

August 3, 2017 Execution Plans Internals 682 Views


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.

Runtime Constants

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:

FROM Customers as c
The output resultset will repeat one single date value across all customers. GETDATE() is 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:

Execution Plans Thick Arrow
The Compute Scalar operator in the execution plan above represents the GETDATE() function. To check if it is a Runtime Constant or not, you need to check the properties of this operator. Here is the properties:
Execution Plans Thick Arrow
The ConstExpr in the Properties of the operator means that it is a Runtime Constant. If it is not a Runtime Constant, it will start with Expr.

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.


FROM Customers as c
WHERE c . Createdat >= CAST ( @MyDateVariable as DATE )

The graphical execution plan in this case does not help much.
Execution Plans Thick Arrow
Even the Properties Window of the two operators in the execution plan does not imply any kind of Expr or ConstExpr. 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.
Execution Plans Thick Arrow
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.

How do you rate this topic?

Further reading:
SQL Server Pages and Extents
Actual vs Estimated Execution Plans
Blocking vs Non Blocking operators
What are the components of an Execution Plan?
What does arrow thickness mean in Execution Plans?