The unusual behavior of common table expressions when using the NewID() function

I love common table expressions. I use them a lot; but I was quite alarmed when using a CTE to update a table (T1) which contained duplicate records. I wanted all records  in T1 with a duplicate ID to share the same uniqueidentifier in column ID2.

To accomplish this a created a CTE which contained a distinct list of ID’s and newid() from T1. I was going to use the CTE to join to T1 on the ID column and use the distinct newid() to update column ID2. I dis not expect what happened next.

First we will need to set up a test environment .

Using the CTE below I expected the following to produce 50 distinct uniqueidentifiers each  repeated three times for each of the distinct duplicate ids.

However when selecting from T1 what we actually see is 150 distinct ids. This got me thinking and I needed to know whether using a temporary table or table value parameter would cause the same unusual results. The code to do this can be found below, but the answer is no. It works as expected.

As it happens this has been asked answered, or at leas a similar question regarding union-ing a CTE containing newid() and receiving unexpected results. Apparently this is intended behavior as the function newid() is intended to be executed on each row the result set. If you are from a OOP background think of it like this temporay tables and table value parameters call newid() on instantiation and CTE calls the function each time the New_ID property is called, this will give an entirely new id each time.

When I started writing this post I had no idea that this was the expected behavior of a common table expression, the following post helped me realise that this was the case.

Hope you enjoyed my rant thoughts, maybe you learned some thing, I know I did.