sql - 在列上执行聚集索引保证返回根据该列排序的行

Question

这个问题已经有答案了:

Does a SELECT query always return rows in the same order? Table with clustered index (1 个回答)

已关闭 8 年前。

对于这个有争议的问题，我无法得到明确的答案。 MSDN documentation提及

聚集

• 聚集索引排序并将数据行存储在表或 View 中基于他们的关键值(value)观。这些是包含在索引定义。每个表只能有一个聚集索引，因为数据行本身只能按一种顺序排序。

• 表中数据行唯一按排序顺序存储的时间是当表包含聚集索引时。当一个表有一个有聚集索引的表称为聚集表。如果一张 table 没有聚集索引，其数据行存储在无序中称为堆的结构。

虽然我看到了大部分答案

• Does a SELECT query always return rows in the same order? Table with clustered index
• http://sqlwithmanoj.com/2013/06/02/clustered-index-do-not-guarantee-physically-ordering-or-sorting-of-rows/

回答是否定的。

这是什么？

Answer 1

只是为了清楚起见。据推测，您正在谈论一个简单的查询，例如:

select *
from table t;

首先，如果表上的所有数据都放在一个页面上，并且表上没有其他索引，我很难想象结果集不按主键排序的场景。然而，这是因为我认为最合理的查询计划需要全表扫描，而不是因为 SQL 或 SQL Server 中的任何要求(记录或其他)。如果没有显式的order by，结果集中的排序是查询计划的结果。

这触及了问题的核心。当您谈论结果集的排序时，您实际上是在谈论查询计划。而且，假设按主键排序实际上意味着您假设查询使用全表扫描。具有讽刺意味的是，人们做出假设，却没有真正理解“为什么”。此外，人们倾向于从小事中进行概括(好吧，这是人类智力基础的一部分)。不幸的是，他们一致认为，对小表进行简单查询的结果集始终按主键顺序排列，并推广到较大的表。本例中的归纳步骤不正确。

什么可以改变这一点？我认为，如果满足以下条件，全表扫描将按主键顺序返回数据:

• 单线程服务器。
• 单个文件文件组
• 没有竞争索引
• 没有表分区

我并不是说这总是正确的。在这种情况下，这样的查询将使用从表开头开始的全表扫描似乎是合理的。

即使在一张小 table 上，你也能得到惊喜。考虑:

select NonPrimaryKeyColumn
from table

查询计划可能会决定在表(NonPrimaryKeyColumn)上使用索引，而不是进行全表扫描。结果不会按主键排序(除非意外)。我展示此示例是因为索引可用于多种目的，而不仅仅是 order by 或 where 过滤。

如果您使用数据库的多线程实例并且拥有合理大小的表，您将很快了解到没有 order by 的结果没有显式排序。

最后，SQL Server 有一个非常智能的优化器。我认为在查询中使用 order by 是有些不愿意的，因为用户认为它会自动进行排序。 SQL Server 努力寻找查询的最佳执行计划。如果它认识到 order by 由于计划的其余部分而显得多余，则 order by 将不会导致排序。

而且，当然你想保证结果的排序，你需要在最外层查询中order by。即使是这样的查询:

select *
from (select top 100 t.* from t order by col1) t

不保证最终结果集中的结果是有序的。你确实需要这样做:

select *
from (select top 100 t.* from t order by col1) t
order by col1;

保证结果按特定顺序排列。此行为已记录在案here .