没有指定排序依据时，SELECT TOP如何工作? [英] how does SELECT TOP works when no order by is specified?

问题描述

msdn文档说，当我们编写

SELECT TOP(N) ..... ORDER BY [COLUMN]

我们得到的前(n)行按column(asc或desc取决于我们选择的内容)排序

We get top(n) rows that are sorted by column (asc or desc depending on what we choose)

但是，如果我们不指定任何顺序，则msdn会说random，因为Gail Erickson指出了此处.正如他指出的那样，应该是unspecified而不是random.但是 Thomas Lee指出

But if we don't specify any order by, msdn says random as Gail Erickson pointed out here. As he points out it should be unspecified rather then random. But as Thomas Lee points out there that

当TOP与ORDER BY子句一起使用时，结果 集合被限制为前N个有序行；否则， 返回前N个随机行

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

因此，我在没有任何索引的表上运行了此查询，首先我运行了它..

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

这总是返回相同的2行，对于查询1返回的所有其他表也是如此.现在执行计划显示3个步骤..

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..

如您所见，没有索引查找，它只是纯表扫描，并且

As you can see there is no index look up, it's just a pure table scan, and

Top显示的实际行数为2，Table Scan也是如此.情况并非如此(我有很多行).

The Top shows actual no of rows to be 2, and so does the Table Scan; Which is not the case (there I many rows).

但是当我运行类似的东西

But when I run something like

SELECT TOP (2) *
FROM
    MstConfigSettings
ORDER BY
    DefaultItemId

执行计划显示

和

因此，当我不应用ORDER BY时，步骤将有所不同(没有排序).但是问题是，当没有Sort时，此TOP如何工作?为什么总是以及如何给出相同的结果?

So, when I don't apply ORDER BY the steps are different (there is no sort). But the question is how does this TOP works when there is no Sort and why and how does it always gives the same result?

推荐答案

不能保证获得哪两行.这只是从表扫描中检索到的前两个.

There is no guarantee which two rows you get. It will just be the first two retrieved from the table scan.

一旦返回两行，执行计划中的TOP迭代器将停止请求行.

The TOP iterator in the execution plan will stop requesting rows once two have been returned.

就像扫描堆一样，这将是分配顺序的前两行，但不能保证.例如，SQL Server可能使用高级扫描功能，这意味着您的扫描将读取最近读取的页面来自另一个并发扫描.

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 scanning feature which means that your scan will read pages recently read from another concurrent scan.

这篇关于没有指定排序依据时，SELECT TOP如何工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！