CTE executed multiple times

user_1297379 0
Viewed 728

I had an issue today with CTEs running on SQL Server 2016, at the moment I worked around it using table variables, however I am not sure if the behavior is wrong or I am reading the documentation wrong.

When you run this query:

with cte(id) as 
(
    select NEWID() as id
)
select * from cte
union all
select * from cte

I would expect two times the same guid, however there are 2 different ones. According to the documentation (https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15) it "Specifies a temporary named result set". However, the above example shows, that it is not a result set, but executed whenever used.

This thread is not about finding a different approach, but much rather checking if there is something wrong with it.

common-table-expression sql-server
1 Answers

Common table expressions are not temporary tables, materialized views, or cached result sets. They are just expressions, and they can be evaluated more than once (which means for functions like NEWID(), you will get a new value each time the expression is evaluated). I touch on this in the "Beware the sugar" portion of this post:

Even in very simple cases, you can see that a CTE that accesses a table once, but is referenced multiple times, ends up evaluating the CTE (and hence accessing the underlying table) multiple times.

And address a similar question here:

Martin Smith has an insightful and thorough answer here:

I do understand that many people make assumptions about how a CTE works, and I appreciate the value that cached CTEs would provide. That's not how they work today, but you can vote on this feedback item to shape that functionality in the future.

You can also ask for clarification in the official documentation - but generally documentation does not list out all of the things that a feature can't do. A common example is, "why doesn't the documentation explicitly state that SELECT * FROM table without an ORDER BY does not guarantee the output in some specific order?" or, more abstractly, "why doesn't my car's owner manual tell me the car can't fly?"

In the meantime, if you want the same value for NEWID() every time you reference the place it came from, you should use a #temp table, @table variable, or local @variable. In your example the change could be as simple as:

declare @newid uniqueidentifier = NEWID();

with cte(id) as 
(
    select @newid as id
)
select * from cte
union all
select * from cte

Example: db<>fiddle

user_613050
Related