Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Sorting working differently after moving from SQL Server 2008 to 2012 RE: Sorting working differently after moving from SQL Server 2008 to 2012

  • You have no order by on that original query, so SQL is under no obligation to return the data to you in any particular order. I suspect the QO is smarter in 2012 and can tell that it can ignore the order by on the insert as there is no identity column.

    An order by on an insert only controls the value of the identity column. Since there's no identity on yours, the ORDER BY can be legally ignored

    If you want data returned in a particular order, the SELECT which returns the data must have an ORDER BY. Otherwise you have no guarantee of order whatsoever.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass