SQL中的Order by子句执行 [英] Order by clause execution in SQL

问题描述

这个问题与执行顺序无关.这只是关于 ORDER BY.

This question isn't about order of executions. It's about just the ORDER BY.

在标准执行中是:

• 来自
• 哪里
• 分组依据
• 拥有
• 选择
• 订购者
• 顶部

这个问题或多或少是在执行 ORDER BY 表达式时 SQL Server 是否应用短路评估?"的问题，答案是有时！我只是没有找到合理的原因.请参阅编辑 #4.

This question has been more or less the issue of "Does SQL Server apply short circuit evaluation when executing ORDER BY expressions?" The answer is SOMETIMES! I just haven't found a reasonable reason as to why. See Edit #4.

现在假设我有这样的声明:

Now suppose I have a statement like this:

DECLARE @dt18YearsAgo AS DATETIME = DATEADD(YEAR,-18,GETDATE());
SELECT
  Customers.Name
FROM
  Customers
WHERE
  Customers.DateOfBirth > @dt18YearsAgo
ORDER BY
  Contacts.LastName ASC, --STATEMENT1
  Contacts.FirstName ASC, --STATEMENT2
  (
   SELECT
     MAX(PurchaseDateTime)
   FROM
     Purchases
   WHERE
     Purchases.CustomerID = Customers.CustomerID
  ) DESC --STATEMENT3

这不是我要执行的真正语句，而只是一个示例.共有三个 ORDER BY 语句.第三条语句仅用于姓氏和名字匹配的极少数情况.

This isn't the real statement I'm trying to execute, but just an example. There are three ORDER BY statements. The third statement is only used for rare cases where the last name and first name match.

如果没有重复的姓氏，SQL Server 是否不执行 ORDER BY 语句 #2 和 #3?并且，从逻辑上讲，如果没有重复的姓氏和名字，SQL Server 是否会注意到执行语句 #3.

If there are no duplicate last names, does SQL Server not execute ORDER BY statements #2 and #3? And, logically, if there are no duplicate last name and first name, does SQL Server note execute statement #3.

这真的是为了优化.从 Purchases 表中读取应该只是最后的手段.就我的应用程序而言，从按CustomerID"分组的Purchases"中读取每个PurchaseDateTime"的效率并不高.

This is really for optimization. Reading from the Purchases table should only be a last resort. In the case of my application, it wouldn't be efficient to read every single "PurchaseDateTime" from "Purchases" grouping by "CustomerID".

请保留与我的问题相关的答案，而不是像在购买中为 CustomerID、PurchaseDateTime 建立索引这样的建议.真正的问题是，SQL Server 是否跳过了不必要的 ORDER BY 语句?

Please keep the answer related to my question and not a suggestion like building an index for CustomerID, PurchaseDateTime in Purchases. The real question is, does SQL Server skip unnecessary ORDER BY statements?

显然，只要有一行，SQL Server 就会始终执行每条语句.即使只有一行，这也会给您除以零误差:

Apparently, SQL Server will always execute every statement as long as there is one row. Even with one row, this will give you a divide by zero error:

DECLARE @dt18YearsAgo AS DATETIME = DATEADD(YEAR,-18,GETDATE());
SELECT
  Customers.Name
FROM
  Customers
WHERE
  Customers.DateOfBirth > @dt18YearsAgo
ORDER BY
  Contacts.LastName ASC, --STATEMENT1
  Contacts.FirstName ASC, --STATEMENT2
  1/(Contacts.ContactID - Contacts.ContactID) --STATEMENT3

显然，这不会除以零:

DECLARE @dt18YearsAgo AS DATETIME = DATEADD(YEAR,-18,GETDATE());
SELECT
  Customers.Name
FROM
  Customers
WHERE
  Customers.DateOfBirth > @dt18YearsAgo
ORDER BY
  Contacts.LastName ASC, --STATEMENT1
  Contacts.FirstName ASC, --STATEMENT2
  CASE WHEN 1=0
    THEN Contacts.ContactID
    ELSE 1/(Contacts.ContactID - Contacts.ContactID)
  END --STATEMENT3

好吧，我的问题的原始答案是肯定的，它确实会执行，但是很好的是我可以使用适当的 CASE WHEN 停止执行

Well, the original answer to my question is YES, it does execute, but what's nice is that I can stop execute with a proper CASE WHEN

编辑 3:我们可以使用适当的 CASE WHEN 停止执行 ORDER BY 语句.我想，诀窍是弄清楚如何正确使用它.CASE WHEN 会给我我想要的，在 ORDER BY 语句中短路执行.我比较了 SSMS 中的执行计划，根据 CASE WHEN 语句，Purchase 表根本没有被扫描，即使它是一个清晰可见的 SELECT/FROM 语句:

Edit 3: We can stop execution of an ORDER BY statement with a proper CASE WHEN. The trick, I guess, is to figure out how to use it properly. CASE WHEN will give me what I want, which a short circuit execution in an ORDER BY statement. I compared the Execution Plan in SSMS and depending on the CASE WHEN statement, the Purchases table isn't scanned at all EVEN THOUGH it's a clearly visible SELECT/FROM statement:

DECLARE @dt18YearsAgo AS DATETIME = DATEADD(YEAR,-18,GETDATE());
SELECT
  Customers.Name
FROM
  Customers
WHERE
  Customers.DateOfBirth > @dt18YearsAgo
ORDER BY
  Contacts.LastName ASC, --STATEMENT1
  Contacts.FirstName ASC, --STATEMENT2
  CASE WHEN 1=0
    THEN
    (
     SELECT
       MAX(PurchaseDateTime)
     FROM
       Purchases
     WHERE
       Purchases.CustomerID = Customers.CustomerID
    )
    ELSE Customers.DateOfBirth
  END DESC

编辑 4:现在我完全糊涂了.这是@Lieven 的一个例子

Edit 4: Now I'm completely confused. Here's an example by @Lieven

WITH Test (name, ID) AS
(SELECT 'Lieven1', 1 UNION ALL SELECT 'Lieven2', 2)

SELECT * FROM Test ORDER BY name, 1/ (ID - ID)

这不会产生除以零的结果，这意味着 SQL Server 实际上会对某些表进行短路评估，特别是那些使用 WITH 命令创建的表.

This yields no divide by zero, which means SQL Server does in fact, do short circuit evaluation on SOME tables, specifically those created with the WITH command.

用 TABLE 变量试试这个:

Trying this with a TABLE variable:

DECLARE @Test TABLE
(
    NAME nvarchar(30),
    ID int
);
INSERT INTO @Test (Name,ID) VALUES('Lieven1',1);
INSERT INTO @Test (Name,ID) VALUES('Lieven2',2);
SELECT * FROM @Test ORDER BY name, 1/ (ID - ID)

将产生除以零误差.

推荐答案

首先，你所说的声明"并不是这样的.它们是 ORDER BY(主要)子句的子子句.区别很重要，因为语句"意味着一些可分离的、有序的和程序性的，而 SQL 子条款不是这些东西.

First of all what you are calling "Statements" are no such thing. They are sub-clauses of the ORDER BY (major) clause. The difference is important, because "Statement" implies something separable, ordered and procedural, and SQL sub-clauses are none of those things.

具体来说，SQL 子条款(即 SQL 主要条款(SELECT、FROM、WHERE、ORDER BY 等)的各个项目)没有自己的隐式(或显式)执行顺序.SQL 会以它认为方便的方式对它们重新排序，并且如果它执行其中任何一个，它几乎总是执行所有.简而言之，SQL Server 不会进行那种短路"优化，因为它们非常有效并且严重妨碍了它所做的非常不同的优化(即统计数据访问/操作员优化).

Specifically, SQL sub-clauses (that is, the individual items of a SQL major clause (SELECT, FROM, WHERE, ORDER BY, etc.)) have no implicit (nor explicit) execution order of their own. SQL will re-order them in anyway that it finds convenient and will almost always execute all of them if it execute any of them. In short, SQL Server does not do that kind of "short-circuit" optimizations because they are trivially effective and seriously get in the way of the very different kind of optimizations that it does do (i.e., Statistical Data Access/Operator Optimizations).

因此，您最初的问题(您不应更改)的正确答案是否定的，不可靠.您不能依赖 SQL Server 不使用 ORDER BY 的某些子句，仅仅因为它看起来不需要.

So the correct answer to your original question (which you should not have changed) is NO, not reliably. You cannot rely on SQL Server to not use some sub-clause of the ORDER BY, simply because it looks like it does not need to.

唯一常见的例外是 CASE 函数可以(在大多数情况下)用于短路执行路径(尽管在 CASE 函数内，而不是在它之外)，但仅因为它是专门为此设计的.我想不出在 SQL 中还有什么其他东西可以依赖您来执行此操作.

The only common exception to this is that the CASE function can (in most circumstances) be used to short-circuit execution paths (within the CASE function though, not outside of it), but only because it is specifically designed for this. I cannot think of anything else in SQL that you can rely on to act like this.

这篇关于SQL中的Order by子句执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！