表值函数 - 输出中忽略排序依据 [英] Table-Valued function - Order by is ignored in output

问题描述

我们正在从 SQL Server 2008 迁移到 SQL Server 2012，并立即注意到我们所有的表值函数不再以正确排序的顺序提供它们的临时表内容.

We are moving from SQL Server 2008 to SQL Server 2012 and immediately noticed that all our table-valued functions no longer deliver their temp table contents in the correctly sorted order.

代码:

INSERT INTO @Customer
        SELECT Customer_ID, Name,
        CASE 
            WHEN Expiry_Date < GETDATE() then 1 
            WHEN Expired = 1 then 1 
            ELSE 0
            END
        from Customer **order by Name**

在 SQL Server 2008 中，此函数返回按名称排序的客户.在 SQL Server 2012 中，它返回未排序的表.order by" 在 SQL 2012 中被忽略.

In SQL Server 2008 this function returns the customers sorted by Name. In SQL Server 2012 it returns the table unsorted. The "order by" is ignored in SQL 2012.

我们是否必须重新编写所有函数以包含 sort_id，然后在主应用程序中调用它们时对它们进行排序，或者是否有简单的修复方法??

Do we have to re-write all the functions to include a sort_id and then sort them when they are called in the main application or is there an easy fix??

推荐答案

你原来的方法有两个问题.

There were two things wrong with your original approach.

1. 在插入表时，永远不能保证 INSERT ... SELECT ... ORDER BY 上的 ORDER BY 将是行的顺序实际插入.
2. 在从中选择时，SQL Server 不保证没有 ORDER BY 的 SELECT 会以任何特定顺序(例如插入顺序)返回行.

1. On inserting to the table it was never guaranteed that the ORDER BY on the INSERT ... SELECT ... ORDER BY would be the order that the rows were actually inserted.
2. On selecting from it SQL Server does not guarantee that SELECT without an ORDER BY will return the rows in any particular order such as insertion order anyway.

在 2012 年，第 1 项的行为似乎发生了变化.现在通常会忽略 SELECT 语句中的 ORDER BY，该语句是插入

In 2012 it looks as though the behaviour has changed with respect to item 1. It now generally ignores the ORDER BY on the SELECT statement that is the source for an INSERT

DECLARE @T TABLE(number int)

INSERT INTO @T 
SELECT number
FROM master..spt_values
ORDER BY name

2008年计划

行为改变的原因是，在以前的版本中，SQL Server 产生了一个在执行之间共享的计划，SET ROWCOUNT 0 (off) 和 SET ROWCOUNT N.排序运算符仅用于确保正确的语义，以防计划由具有非零 ROWCOUNT 集的会话运行.它左侧的 TOP 运算符是 ROWCOUNT TOP.

The reason for the change of behaviour is that in previous versions SQL Server produced one plan that was shared between executions with SET ROWCOUNT 0 (off) and SET ROWCOUNT N. The sort operator was only there to ensure the correct semantics in case the plan was run by a session with a non zero ROWCOUNT set. The TOP operator to the left of it is a ROWCOUNT TOP.

SQL Server 2012 现在为这两种情况生成单独的计划，因此无需将它们添加到计划的 ROWCOUNT 0 版本中.

SQL Server 2012 now produces separate plans for the two cases so there is no need to add these to the ROWCOUNT 0 version of the plan.

如果 SELECT 明确定义了 TOP(除了 TOP 100 PERCENT)，但 2012 年的计划中仍可能出现排序这仍然不能保证行的实际插入顺序，在建立 TOP N 之后，计划可能会有另一种排序，例如将行放入聚集索引顺序.

A sort may still appear in the plan in 2012 if the SELECT has an explicit TOP defined (other than TOP 100 PERCENT) but this still doesn't guarantee actual insertion order of rows, the plan might then have another sort after the TOP N is established to get the rows into clustered index order for example.

对于您问题中的示例，如果需要，我将调整调用代码以指定 ORDER BY 名称.

For the example in your question I would just adjust the calling code to specify ORDER BY name if that is what it requires.

关于您的 sort_id 想法来自 SQL Server 中的排序保证 保证在使用 IDENTITY 插入表时，这些分配的顺序将按照 ORDER BY 进行，因此您也可以

Regarding your sort_id idea from Ordering guarantees in SQL Server it is guaranteed when inserting into a table with IDENTITY that the order these are allocated will be as per the ORDER BY so you could also do

DECLARE @Customer TABLE (
  Sort_Id     INT IDENTITY PRIMARY KEY,
  Customer_ID INT,
  Name        INT,
  Expired     BIT )

INSERT INTO @Customer
SELECT Customer_ID,
       Name,
       CASE
         WHEN Expiry_Date < Getdate() THEN 1
         WHEN Expired = 1 THEN 1
         ELSE 0
       END
FROM   Customer
ORDER  BY Name 

但是您仍然需要在选择查询中按 sort_id 排序，因为没有它就不能保证排序(也许这种 sort_id 方法在这种情况下可能有用其中用于排序的原始列不会被复制到表变量中)

but you would still need to order by the sort_id in your selecting queries as there is no guaranteed ordering without that (perhaps this sort_id approach might be useful in the case where the original columns used for ordering aren't being copied into the table variable)

这篇关于表值函数 - 输出中忽略排序依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！