tsql - Why does newid() materialize at the very end of a query? -

if run following sample code in sql server, you'll notice newid() materializes after join whereas row_number() materializes before join. understand , if there's way work around it?

declare @a table ( num varchar(10) )  insert @a values ('dan')  insert @a values ('dan')  insert @a values ('fran')  insert @a values ('fran')   select *    @a t             inner join         (select num, newid() id            @a           group num) t1      on t1.num = t.num            select *    @a t             inner join         (select num, row_number() on (order num) id            @a           group num) t1      on t1.num = t.num 

not sure see problem here. materialize subquery t1 first:

select num, row_number() on (order num)      @a     group num; 

you 2 rows:

dan  1 fran 2 

now join against on num = num, 4 rows, 2 each distinct value. actual goal here? perhaps should applying row_number() outside?

the order of materialization optimizer. you'll find other built-ins (rand(), getdate() etc.) have inconsistent materialization behavior. not can it, , not chance they're going "fix" it.

edit

new code sample. write contents of @a #temp table "materialize" newid() assignment per unique num value.

select num, id = newid()    #foo @a group num;  select a.num, f.id    @a    inner join #foo f    on a.num = f.num;  drop table #foo;