sql - 表值函数 - 输出中忽略排序依据

Question

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

代码:

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 中，它返回未排序的表。 SQL 2012 中会忽略“order by”。

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

Answer 1

您原来的方法有两个问题。

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

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

DECLARE @T TABLE(number int)

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

2008年计划

2012年计划

行为更改的原因是，在以前的版本中，SQL Server 生成了一个计划，该计划在使用 SET ROWCOUNT 0(关闭)和 SET ROWCOUNT N 的执行之间共享。排序运算符仅用于在计划由具有非零 ROWCOUNT 集的 session 运行时确保正确的语义。其左侧的 TOP 运算符是 ROWCOUNT TOP .

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

如果 SELECT 明确定义了 TOP(TOP 100 PERCENT 除外)，则排序可能仍会出现在 2012 年的计划中，但是这仍然不能保证行的实际插入顺序，例如，在建立 TOP N 后，计划可能会进行另一种排序，以使行符合聚集索引顺序。

对于您问题中的示例，我只需调整调用代码以指定 ORDER BY name(如果这是它所需要的)。

关于 Ordering guarantees in SQL Server 中您的 sort_id 想法当插入带有 IDENTITY 的表时，可以保证这些分配的顺序将按照 ORDER BY 进行，因此您也可以这样做

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 方法在这种情况下可能有用其中用于排序的原始列未复制到表变量中)