Table-Valued function - Order by is ignored in out

Question

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.

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**

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.

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??

Answer 1

add a column named rowno to @Customer table

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 

Answer 2

There were two things wrong with your original approach.

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.

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 Plan

2012 Plan

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 now produces separate plans for the two cases so there is no need to add these to the ROWCOUNT 0 version of the plan.

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.

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

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 

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)