2

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://learn.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.

Philipp
  • 1,425
  • 1
  • 11
  • 23
  • 2
    CTE is Common Table **Expression**, I assume if it would be a ready result set it would be named as such ;) – Fabio Jun 10 '20 at 20:15
  • 4
    See the section "Beware the sugar" in this post: https://www.sentryone.com/blog/aaronbertrand/backtobasics-ctes - if you want something like `NEWID()` only evaluated once, then use a local variable instead of a CTE. – Aaron Bertrand Jun 10 '20 at 20:20
  • @AaronBertrand thank you, that's actually confirming what I thought. But still, that's a real downside of CTEs. One of the great advantages would be to use the result multiple times (as I did in my original query) which is then always bad for the performance of the query. I really wonder why there is no remark in the documentation. – Philipp Jun 10 '20 at 20:38
  • 1
    If you want to use the result set, use a temp table. CTEs have never been advertised or documented as a reusable, static resultset, though many people make assumptions about that. It's not documented for the same reason the user manual for my BMW doesn't say the car can't fly. Imagine if all documentation had to list all of the things the feature can't do? – Aaron Bertrand Jun 10 '20 at 20:40
  • 1
    [You can ask for an explicit reference in the documentation](https://i.stack.imgur.com/opBu4.png) but, now you know, and this isn't a technical problem your peers can solve for you. There is nothing wrong with the CTE or your code, you just expected it to work differently than how it works. – Aaron Bertrand Jun 10 '20 at 20:45

1 Answers1

6

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 or this feedback item to shape that functionality in the future. Sadly, the new feedback system is not very popular, and popularity drives the work, so only a handful of feedback items have more than a handful of votes. I think the community has done a fool me once thing here after how they handled Connect.

You can also ask them to add 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

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • I'm not so sure MS will ever touch materializing CTEs, seeing as the first comment of them is from 2009... – TT. Jun 11 '20 at 14:44
  • @TT. It took them 15 years to fix "string or binary would be truncated" so maybe use a thinner brush. :-) – Aaron Bertrand Jun 11 '20 at 14:46
  • I upvoted that item anyway, let's see if they touch it before I retire ;-) – TT. Jun 11 '20 at 14:56