为什么 newid() 在查询的最后实现? [英] Why does newid() materialize at the very end of a query?

问题描述

如果您在 SQL Server 中运行以下示例代码，您会注意到 newid() 在联接之后具体化，而 row_number() 在联接之前具体化.有没有人理解这一点，是否有办法解决这个问题?

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 understand this and if there's a way to work around it?

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

推荐答案

我不确定这里有什么问题.先具体化子查询T1:

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

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

你得到两行:

dan  1
fran 2

现在将它与 num = num 上的 a 连接起来，您将得到 4 行，每个不同的值对应 2 行.你在这里的实际目标是什么?也许你应该在外面申请 ROW_NUMBER() ?

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?

具体化的顺序由优化器决定.您会发现其他内置函数(RAND()、GETDATE() 等)具有类似的不一致的实现行为.您对此无能为力，而且他们修复"它的机会也不大.

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.

编辑

新的代码示例.将@a 的内容写入 #temp 表，以具体化"每个唯一 num 值的 NEWID() 分配.

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 @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;

这篇关于为什么 newid() 在查询的最后实现?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！