The SQL Server query optimizer can combine repeated calculated values into a single Compute Scalar operator. Whether or not it will do this depends on query plan costing and the properties of the calculated value. As expected, it will not do this for calculated values which are nondeterministic, which a few exceptions such as
RAND(). It also will not do this for user defined functions.
I will start with a user defined function example. Here is an excellent example of a user defined function:
CREATE OR ALTER FUNCTION dbo.NULL_FUNCTION (@N BIGINT) RETURNS BIGINT
I also want to create a table and to put 100 rows into it:
CREATE TABLE X_100 (N BIGINT NOT NULL);
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT INTO X_100 WITH (TABLOCK)
FROM Nums WHERE n <= 100;
dbo.NULL_FUNCTION function is determistic. How many times will it be executed for the following query?
SELECT n, dbo.NULL_FUNCTION(n)
Based on the query plan this will be executed once for each row, or 100 times:
SQL Server 2016 introduced the sys.dm_exec_function_stats DMV. We can take snapshots of that DMV to see how many times a UDF is executed by a query.
WHERE object_id = OBJECT_ID('NULL_FUNCTION');
The result of that is 100, so the function was executed 100 times.
Let's try another simple query:
SELECT n, dbo.NULL_FUNCTION(n), dbo.NULL_FUNCTION(n)
The query plan suggests that the function will be executed 200 times:
The results of
sys.dm_exec_function_stats suggest that the function was executed 200 times.
Note that you cannot always use the query plan to figure out how many times a compute scalar is executed. The following quote is from "Compute Scalars, Expressions and Execution Plan Performance":
This leads people to think that Compute Scalar behaves like the majority of other operators: as rows flow through it, the results of whatever computations the Compute Scalar contains are added to the stream. This is not generally true. Despite the name, Compute Scalar does not always compute anything, and does not always contain a single scalar value (it can be a vector, an alias, or even a Boolean predicate, for example). More often than not, a Compute Scalar simply defines an expression; the actual computation is deferred until something later in the execution plan needs the result.
Let's try another example. For the following query I would hope that the UDF is calculated one time:
WITH NULL_FUNCTION_CTE (NULL_VALUE) AS
SELECT DISTINCT dbo.NULL_FUNCTION(0)
SELECT n , cte.NULL_VALUE
CROSS JOIN NULL_FUNCTION_CTE cte;
The query plan suggests that it will be calculated one time:
However, the DMV reveals the truth. The compute scalar is deferred until it is needed, which is in the join operator. It is evaluated 100 times.
You also asked what you can do to encourage the optimizer to avoid recalculating the same expression multiple times. The best thing that you can do is to avoid using scalar UDFs in your code. Those have a number of performance issues outside of this question, including inflating memory grants, forcing the entire query to run with
MAXDOP 1, bad cardinality estimates, and leading to additional CPU utilization. If you do need to use a UDF and the value of that UDF is a constant you can calculate it outside of the query and to put it in a local variable.
For queries without UDFs, you can try to avoid writing expressions which return the same result but aren't typed in exactly the same way. For this next example I'm using the publicly available AdventureworksDW2016CTP3 database, but really any database will do. How many times will
COUNT(*) be calculated for this query?
SELECT OrderDateKey, COUNT(*)
GROUP BY OrderDateKey
ORDER BY COUNT(*) DESC;
For this query we can figure this out by looking at the Hash Match (aggregate) operator.
COUNT(*) is computed once for each unique value of
OrderDateKey. Including the
ORDER BY clause does not cause it to be calculated twice. You can see the execution plan here.
Now, consider a query that will return the exact same results but is written in a different way:
SELECT OrderDateKey, SUM(1)
GROUP BY OrderDateKey
ORDER BY COUNT(*) DESC;
The query optimizer is not smart enough to combine them, so additional work will be done: