SELECT查询中的默认行顺序-SQL Server 2008与SQL 2012 [英] Default row order in SELECT query - SQL Server 2008 vs SQL 2012

问题描述

我们的团队最近将数据库从SQL Server 2008升级到了SQL Server2012。我们注意到的一个重大变化是SELECT语句返回的行的默认顺序，即未指定显式的ORDER BY子句时。

Our team recently upgraded our databases from SQL Server 2008 to SQL Server 2012. One breaking change we noticed was in the default order of rows returned by the SELECT statement, i.e. when an explicit ORDER BY clause is not specified.

根据MSDN， SQL除非指定了ORDER BY子句，否则Server 2012 不会保证返回的行顺序。

As per MSDN, SQL Server 2012 does not gaurantee the order of the rows returned unless an ORDER BY clause is specified.

我们在5个具有SELECT的数据库中拥有2500多个存储过程语句中没有ORDER BY子句，并且要手动添加ORDER BY子句以匹配SQL Server 2008中的行为将是一项巨大的工作。是否有设置或更快的方式来做到这一点？

We have 2500+ stored procedures across 5 databases that have SELECT statements without an ORDER BY clause and it will be a sizeable effort to add the ORDER BY clause manually to match the behavior in SQL Server 2008. Is there a setting or faster way of doing this?

尚未探索的另一个选择是降级到SQL Server2008。这有多难？

The other option, which hasn't been explored, is to downgrade to SQL Server 2008. How difficult would this be?

推荐答案

您需要返回并在代码中添加 ORDER BY 子句，因为没有永远不会保证顺序。过去，您很幸运，总是获得相同的订单，但这并不是因为SQL Server 2008始终保证了订单的执行。

You need to go back and add ORDER BY clauses to your code because without them the order is never guaranteed. You were "lucky" in the past that you always got the same order but it wasn't because SQL Server 2008 guaranteed it in anyway. It most likely had to do with your indexes or how the data was being stored on the disk.

如果在升级硬件配置上的差异时移至新主机，则很可能与索引或数据在磁盘上的存储方式有关。仅此一项可能会改变查询的执行方式。更不用说新服务器将重新计算表的统计信息，并且SQL Server 2012查询优化器的功能可能与SQL Server 2008中的有所不同。

If you moved to a new host when you upgraded the difference in hardware configuration alone could have changed the way your queries execute. Not to mention the fact that the new server would have recalculated statistics on the tables and the SQL Server 2012 query optimizer probably does things a bit differently than the one in SQL Server 2008.

这是一个谬论，您可以依靠SQL中结果集的顺序，而无需明确说明所需的顺序。SQL结果从不具有可以依靠的顺序，而无需使用 ORDER BY 子句。 SQL是基于集合论构建的。查询结果基本上是集合（或多集合）。

It is a fallacy that you can rely on the order of a result set in SQL without explicitly stating the order you want it in. SQL results NEVER have an order you can rely on without using an ORDER BY clause. SQL is built around set theory. Query results are basically sets (or multi-sets).

Itzik Ben-Gan在他的书 Microsoft SQL Server 2012 T-SQL基础

Itzik Ben-Gan gives a good description of set theory in relation to SQL in his book Microsoft SQL Server 2012 T-SQL Fundamentals

集合理论起源于数学家Georg Cantor，是
的数学分支之一，其关系模型基于
。康托尔对集合的定义如下：

Set theory, which originated with the mathematician Georg Cantor, is one of the mathematical branches on which the relational model is based. Cantor's definition of a set follows:

集合是指将任何集合M放入一个完整的，明确的
对象m中（称为对象M）的元素或我们的思想的
。 -约瑟夫·道本（Joseph W. Dauben）和格奥尔格·坎托（Georg Cantor）（普林斯顿大学出版社，1990年，普林斯顿大学出版社）

By a "set" we mean any collection M into a whole of definite, distinct objects m (which are called the "elements" of M) of our perception or of our thought. - Joseph W. Dauben and Georg Cantor (Princeton University Press, 1990)

然后定义Itzik继续说：

After a thorough explanation of the terms in the definition Itzik then goes on to say:

Cantor对集合的定义所遗漏的内容可能与
一样重要包括在内。请注意，定义未提及set元素中的任何
订单。列出集合元素
的顺序并不重要。列出集合元素
的形式符号使用大括号：{a，b，c}。由于订单没有相关性，因此您可以
表示与{b，a，c}或{b，c，a}相同的集合。跳转到构成
关系（在SQL中称为表）的标头的
属性集（在SQL中称为列），一个元素应该是通过名称标识的
-非顺序位置。同样，考虑组成关系主体的元组的集合
（在SQL中称为行）。
元素由其键值而非位置确定。许多
程序员很难适应这样的想法，即相对于
查询表，行之间没有顺序。 换句话说，对表的
a查询可以按任何顺序返回行，除非您
明确要求以特定方式对数据进行排序，也许
是出于演示目的。

但是，无论对集合的学术定义是什么，甚至SQL Server中的实现也无法保证。结果中的任何顺序。此 MSDN博客发自2005年，由查询优化器成员提供团队指出，您根本不应该依赖中间操作的顺序。

But regardless of the academic definition of a set even the implementation in SQL server has never guaranteed any order in the results. This MSDN blog post from 2005 by a member of the query optimizer team states that you should not rely on the order from intermediate operations at all.

重新排序规则可能并且会违反此规则假设（如果开发人员不方便，请这样做
；）。请理解
，当我们对操作进行重新排序以找到更有效的计划时，我们可以使
改变
树中中间节点的排序行为。如果您在树中进行了假设
特定中间顺序的操作，则该操作可能会中断。

The reordering rules can and will violate this assumption (and do so when it is inconvenient to you, the developer ;). Please understand that when we reorder operations to find a more efficient plan, we can cause the ordering behavior to change for intermediate nodes in the tree. If you’ve put an operation in the tree that assumes a particular intermediate ordering, it can break.

此博客由Conor Cunningham（架构师，SQL Server核心引擎）发布 没有安全带-预期没有ORDER BY的订单是关于SQL Server 2008的。他有一个表，该表中有2万行，其中一个索引似乎总是返回行以相同的顺序。向查询中添加 ORDER BY 甚至不会更改执行计划，因此，如果优化程序意识到添加一个in使得查询更昂贵，这不像是在添加查询。不需要它。但是，一旦他又向表中添加了2万行，查询计划便突然改变，现在它使用了并行性，并且结果不再排序！

This blog post by Conor Cunningham (Architect, SQL Server Core Engine) "No Seatbelt - Expecting Order without ORDER BY" is about SQL Server 2008. He has a table with 20k rows in it with a single index that appears to always return rows in the same order. Adding an ORDER BY to the query doesn't even change the execution plan, so it isn't like adding one in makes the query more expensive if the optimizer realizes it doesn't need it. But once he adds another 20k rows to the table suddenly the query plan changes and now it uses parallelism and the results are no longer ordered!

这里的难点是，任何外部
用户没有合理的方式知道计划何时更改。所有计划的空间都是巨大的
，令人难以思考。如果更改了足够多的参数，SQL Server的优化程序将更改
计划，即使是简单查询也是如此。
您可能会很幸运并且没有计划变更，或者您根本不会
考虑这个问题并添加ORDER BY。

The hard part here is that there is no reasonable way for any external user to know when a plan will change . The space of all plans is huge and hurts your head to ponder. SQL Server's optimizer will change plans, even for simple queries, if enough of the parameters change. You may get lucky and not have a plan change, or you can just not think about this problem and add an ORDER BY.

如果您需要更多说服力，请阅读以下文章：

If you need more convincing just read these posts:

• 没有ORDER BY，则没有默认排序顺序。 -亚历山大·库兹涅佐夫（Alexander Kuznetsov）


• 在法院下达命令！ -托马斯·基特（Thomas Kyte）


• SQL中结果集的顺序-Timothy Wiseman

• Without ORDER BY, there is no default sort order. - Alexander Kuznetsov
• Order in the court! - Thomas Kyte
• Order of a Result Set in SQL - Timothy Wiseman

这篇关于SELECT查询中的默认行顺序-SQL Server 2008与SQL 2012的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！