Why Does Newid() Materialize At The Very End Of A Query?

If you run the following sample code in SQL Server, you'll notice that newid() materializes after the join whereas row_number() materializes before the join. Does anyone understan

Solution 1:

i had a similar problem and found out that the "inner join" was the problem. i was able to use "left joins" ...

Solution 2:

Not sure I see what the problem is here. Materialize the subquery T1 first:

SELECT num, ROW_NUMBER() OVER (ORDERBY num) 
    FROM@aGROUPBY num;

You get two rows:

dan  1
fran 2

Now join that against a on num = num, you get 4 rows, 2 for each distinct value. What is your actual goal here? Perhaps you should be applying ROW_NUMBER() outside?

The order of materialization is up to the optimizer. You'll find that other built-ins (RAND(), GETDATE() etc.) have similarly inconsistent materialization behavior. Not much you can do about it, and not much chance they're going to "fix" it.

EDIT

New code sample. Write the contents of @a to a #temp table to "materialize" the NEWID() assignment per unique num value.

SELECT num, id = NEWID() 
  INTO #foo FROM@aGROUPBY num;

SELECT a.num, f.id 
  FROM@aAS a 
  INNERJOIN #foo AS f 
  ON a.num = f.num;

DROPTABLE #foo;