Функция Table-Value - Order by игнорируется на выходе

Мы переходим от SQL Server 2008 к SQL Server 2012 и сразу же заметили, что все наши табличные функции больше не доставляют содержимое временной таблицы в правильно отсортированном порядке.

CODE:

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 он возвращает таблицу unsorted. "порядок игнорируется в SQL 2012.

Нужно ли переписывать все функции для включения sort_id, а затем сортировать их, когда они вызываются в основном приложении, или есть простое исправление?

Ответ 1

В вашем первоначальном подходе были две вещи.

При вставке в таблицу никогда не гарантировалось, что ORDER BY в INSERT ... SELECT ... ORDER BY будет порядком, в котором строки были фактически вставлены.
При выборе из него SQL Server не гарантирует, что SELECT без ORDER BY будет возвращать строки в любом конкретном порядке, например, порядок вставки.

В 2012 году похоже, что поведение изменилось в отношении пункта 1. Теперь он обычно игнорирует ORDER BY в операторе SELECT, который является источником для INSERT

DECLARE @T TABLE(number int)

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

План 2008

План 2012

Причиной изменения поведения является то, что в предыдущих версиях SQL Server создал один план, который был разделен между исполнениями с SET ROWCOUNT 0 (off) и SET ROWCOUNT N. Оператор sort был только там, чтобы обеспечить правильную семантику в случае, если план выполнялся сеансом с набором ноль ROWCOUNT. Оператор TOP слева от него является ROWCOUNT TOP.

SQL Server 2012 теперь производит отдельные планы для двух случаев, поэтому нет необходимости добавлять их в версию плана ROWCOUNT 0.

Сорт может по-прежнему отображаться в плане в 2012 году, если SELECT имеет явный TOP определенный (кроме TOP 100 PERCENT), но это все еще не гарантирует фактический порядок вставки строк, тогда план может иметь другой тип после того, как TOP N установлен, чтобы, например, получить строки в кластерный индексный порядок.

Для примера в вашем вопросе я бы просто отредактировал вызывающий код, чтобы указать ORDER BY name, если это то, что ему нужно.

Относительно вашей идеи sort_id от Заказ гарантий в SQL Server гарантируется при вставке в таблицу с 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 может быть полезен в случае, когда исходные столбцы, используемые для заказа, не являются копируется в переменную таблицы)

Ответ 2

добавить столбец с именем rowno в таблицу @Customer

INSERT INTO @Customer

SELECT ROW_NUMBER()over(order by Name)rowno,Customer_ID, Name,
        CASE 
            WHEN Expiry_Date < GETDATE() then 1 
            WHEN Expired = 1 then 1 
            ELSE 0
            END
from Customer