“按 newid() 排序"；- 它是如何工作的? [英] "order by newid()" - how does it work?

问题描述

我知道如果我运行这个查询

通过 newid() 从 mytable order 中选择 top 100 *

它将从我的表中随机获取 100 条记录.

但是，我对它的工作方式有点困惑，因为我在 select 列表中没有看到 newid().有人可以解释一下吗?这里的 newid() 有什么特别之处吗?

解决方案

我知道 NewID() 的作用，我只是试图了解它会如何帮助在随机选择中.是那个吗(1) select语句会选择来自 mytable 的一切，(2) 每个选定行，添加一个由 NewID() 生成的唯一标识符，(3) 按此对行进行排序uniqueidentifier 和 (4) 选择排序列表中的前 100 个?

是的.这几乎完全正确(除非它不一定需要对所有行进行排序).您可以通过查看实际执行计划来验证这一点.

SELECT TOP 100 *FROM master..spt_values按 NEWID() 排序

计算标量运算符为每一行添加 NEWID() 列(在我的示例查询中的表中为 2506)，然后表中的行按此列排序，并选择前 100.

SQL Server 实际上不需要从位置 100 向下对整个集合进行排序，因此它使用 TOP N 排序运算符尝试在内存中执行整个排序操作(

I know that If I run this query

select top 100 * from mytable order by newid()

it will get 100 random records from my table.

However, I'm a bit confused as to how it works, since I don't see newid() in the select list. Can someone explain? Is there something special about newid() here?

解决方案

I know what NewID() does, I'm just trying to understand how it would help in the random selection. Is it that (1) the select statement will select EVERYTHING from mytable, (2) for each row selected, tack on a uniqueidentifier generated by NewID(), (3) sort the rows by this uniqueidentifier and (4) pick off the top 100 from the sorted list?

Yes. this is pretty much exactly correct (except it doesn't necessarily need to sort all the rows). You can verify this by looking at the actual execution plan.

SELECT TOP 100 * 
FROM master..spt_values 
ORDER BY NEWID()

The compute scalar operator adds the NEWID() column on for each row (2506 in the table in my example query) then the rows in the table are sorted by this column with the top 100 selected.

SQL Server doesn't actually need to sort the entire set from positions 100 down so it uses a TOP N sort operator which attempts to perform the entire sort operation in memory (for small values of N)

这篇关于“按 newid() 排序"；- 它是如何工作的?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！