Why does newid () materialize at the very end of the request?

Question

If you run the following code example in SQL Server, you will notice that newid () materializes after the connection, while row_number () materializes before the connection. Does anyone understand this and if there is a way around this?

declare @a table ( num varchar(10) ) insert into @a values ('dan') insert into @a values ('dan') insert into @a values ('fran') insert into @a values ('fran') select * from @a T inner join (select num, newid() id from @a group by num) T1 on T1.num = T.num select * from @a T inner join (select num, row_number() over (order by num) id from @a group by num) T1 on T1.num = T.num 

Answer 1

Not sure if I see what the problem is. First run subquery T1:

 SELECT num, ROW_NUMBER() OVER (ORDER BY num) FROM @a GROUP BY num; 

You get two lines:

 dan 1 fran 2 

Now join it with the number num = num, you will get 4 lines, 2 for each individual value. What is your actual goal? Perhaps you should use ROW_NUMBER () outside?

The order of materialization depends on the optimizer. You will find that other built-in modules (RAND (), GETDATE (), etc.) have similar inconsistent materialization behavior. There is not much you can do with this, and there is not much chance that they are going to “fix” it.

EDIT

New sample code. Write the contents of @a to the #temp table to “materialize” the NEWID () assignment for each unique num value.

 SELECT num, id = NEWID() INTO #foo FROM @a GROUP BY num; SELECT a.num, f.id FROM @a AS a INNER JOIN #foo AS f ON a.num = f.num; DROP TABLE #foo; 

Answer 2

I had a similar problem, and it turned out that the “internal connection” problem was a problem. I was able to use the "left connections" ...