SQL 当未指定 order by 时，SELECT TOP 如何工作？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15245322/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how does SELECT TOP works when no order by is specified?
提问by Razort4x
The msdn documentationsays that when we write
在MSDN文档说，当我们写
SELECT TOP(N) ..... ORDER BY [COLUMN]
We get top(n) rows that are sorted by column(ascor descdepending on what we choose)
我们得到按column（asc或desc取决于我们选择的内容）排序的前（n）行
But if we don't specify any order by, msdn says randomas Gail Ericksonpointed out here. As he points out it should be unspecifiedrather then random. But as Thomas Leepoints out there that
但是，如果我们不指定任何 order by，msdn 会random按照此处Gail Erickson指出的那样说。正如他指出的那样，应该是这样。但正如指出的那样unspecifiedrandomThomas Lee
When TOP is used in conjunction with the ORDER BY clause, the result set is limited to the first N number of ordered rows; otherwise, it returns the first N number of rows ramdom
当TOP与ORDER BY子句结合使用时，结果集限制为前N个有序行；否则，它随机返回前 N 行
So, I ran this query on a table that doesn't have any indexes, first I ran this..
所以，我在没有任何索引的表上运行了这个查询，首先我运行了这个..
SELECT *
FROM
sys.objects so
WHERE
so.object_id NOT IN (SELECT si.object_id
FROM
sys.index_columns si)
AND so.type_desc = N'USER_TABLE'
And then in one of those tables, (in fact I tried the query below in all of those tables returned by above query) and I always got the same rows.
然后在其中一个表中（实际上，我在上述查询返回的所有表中尝试了下面的查询）并且我总是得到相同的行。
SELECT TOP (2) *
FROM
MstConfigSettings
This always returned the same 2 rows, and same is true for all other tables returned by query 1. Now the execution plans shows 3 steps..
这总是返回相同的 2 行，对于查询 1 返回的所有其他表也是如此。现在执行计划显示 3 个步骤..
As you can see there is no index look up, it's just a pure table scan, and
正如你所看到的，没有索引查找，它只是一个纯粹的表扫描，并且
The Topshows actual no of rows to be 2, and so does the Table Scan; Which is not the case (there I many rows).
的Top行示出了实际不为2，也是如此的Table Scan; 情况并非如此（我有很多行）。
But when I run something like
但是当我运行类似的东西时
SELECT TOP (2) *
FROM
MstConfigSettings
ORDER BY
DefaultItemId
The execution plan shows
执行计划显示
and
和
So, when I don't apply ORDER BYthe steps are different (there is no sort). But the question is how does this TOPworks when there is no Sortand why and how does it alwaysgives the same result?
所以，当我不应用时ORDER BY，步骤是不同的（没有排序）。但问题是TOP当没有时这如何工作Sort，为什么以及如何总是给出相同的结果？
回答by Martin Smith
There is no guarantee which two rows you get. It will just be the first two retrieved from the table scan.
无法保证您得到哪两行。它只是从表扫描中检索到的前两个。
The TOPiterator in the execution plan will stop requesting rows once two have been returned.
TOP一旦返回两个行，执行计划中的迭代器将停止请求行。
Likely for a scan of a heap this will be the first two rows in allocation order but this is not guaranteed. For example SQL Server might use the advanced scanningfeature which means that your scan will read pages recently read from another concurrent scan.
可能对于堆扫描，这将是分配顺序中的前两行，但这不能保证。例如，SQL Server 可能使用高级扫描功能，这意味着您的扫描将读取最近从另一个并发扫描中读取的页面。
