Sorting working differently after moving from SQL Server 2008 to 2012

  • I have a stored procedure that is suddenly returning data in a different order, and I'm wondering if this is a by-product of moving from SQL Server 2008 to 2012. The original stored procedure SELECT statement (which used to return a list of names sorted by LastName, FirstName) is:

    SELECT

    EmpLogin,

    AttorneyName = LastName + ', ' + FirstName

    INTO #Partner

    FROM

    (

    SELECT

    EmpLogin = RTRIM(LTRIM(EmpLogin)) + '|' + RIGHT(PhoneNumber,4),

    FirstName,

    LastName

    FROM dbo.Employees

    WHERE JobCode BETWEEN 100 AND 500

    AND EmpTermDate IS NULL

    ORDER BY LastName, FirstName

    ) x

    SELECT EmpLogin = '-1', AttorneyName = 'Please Select'

    UNION ALL

    SELECT

    EmpLogin,

    AttorneyName

    FROM #Partner

    We moved from SQL Server 2008 to 2012 recently, and yesterday a user called to complain that a dropdown list (which is populated by the above query) was no longer sorted. Sure enough, it wasn't, and even when I just ran the stored procedure in SSMS, I got an unsorted list. To correct the problem, I modified the SELECT statement in the stored proc like so:

    SELECT

    EmpLogin,

    AttorneyName = LastName + ', ' + FirstName

    INTO #Partner

    FROM

    (

    SELECT

    EmpLogin = RTRIM(LTRIM(EmpLogin)) + '|' + RIGHT(PhoneNumber,4),

    FirstName,

    LastName

    FROM dbo.Employees

    WHERE JobCode BETWEEN 100 AND 500

    AND EmpTermDate IS NULL

    ) x

    SELECT EmpLogin = '-1', AttorneyName = 'Please Select'

    UNION ALL

    SELECT

    EmpLogin,

    AttorneyName

    FROM #Partner

    ORDER BY AttorneyName

    Could this be a by-product of the change from 2008 to 2012, or is there something else going on here?

  • 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
  • That said, I suspect you left something out, as the original query, on SQL 2008 R2, returns

    Msg 1033, Level 15, State 1, Line 15

    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

    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
  • The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless either the TOP or OFFSET and FETCH clauses are also specified. It hasn't been in a long time. It shouldn't have worked in 2008 either. https://msdn.microsoft.com/en-us/library/ms188385(v=sql.100).aspx

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Whoops. The ORDER BY clause in the original SELECT statement should've come after the "x" alias. I was typing it from memory since I'd already changed it on the server so it would act correctly, and I goofed. But thanks for your comments. As Gail says, perhaps it's just a question of 2012 being "smarter" and knowing that it didn't have to return the data in any particular order.

  • Even in 2008, you could and would at some point get different ordering, since there was no order by on the outer select.

    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
  • I'm sure you know this, but in case not: you can add an IDENTITY to a SELECT ... INTO #TempTable :

    SELECT

    [highlight="#ffff11"]MyID = IDENTITY(int, 1, 1),[/highlight]

    EmpLogin,

    AttorneyName = LastName + ', ' + FirstName

    INTO #Partner

    ...

  • Kristen-173977 (10/9/2015)


    I'm sure you know this, but in case not: you can add an IDENTITY to a SELECT ... INTO #TempTable :

    SELECT

    [highlight="#ffff11"]MyID = IDENTITY(int, 1, 1),[/highlight]

    EmpLogin,

    AttorneyName = LastName + ', ' + FirstName

    INTO #Partner

    ...

    And if you do that, and add an order by on the insert, the order by will define the order that the identity values are assigned, not the order the rows are selected out of the table. Adding such an identity will not change the fact that tables, by definition, are unordered sets of rows, nor that without an order by on the final select, there is 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
  • Good point Gail, thanks. When we do this we do so that we can just use ORDER BY MyID in other queries to avoid having to store all the Sort Order keys in the #TEMP table, and so we can create a, narrow, Clustered Index (perhaps as a PKey) on MyID in the #TEMP table

    It also sometimes helps, i.e. when using ORDER BY MyID in other queries, as the intended sort order columns does not need to be known, so in DEV we can change the original INSERT statement's ORDER BY without having to change all the subsequent SELECTs

  • GilaMonster (10/9/2015)


    And if you do that, and add an order by on the insert, the order by will define the order that the identity values are assigned, not the order the rows are selected out of the table.

    Your saying that made me wonder if it would be better just to add a unique number using ROW_NUMBER() OVER rather than IDENTITY - maybe it would be less effort for SQL?

    If inserting more rows in subsequent steps then IDENTITY is a help of course ... but for a single SELECT .. INTO #TEMP I wonder which is better?

  • Kristen-173977 (10/9/2015)


    Your saying that made me wonder if it would be better just to add a unique number using ROW_NUMBER() OVER rather than IDENTITY - maybe it would be less effort for SQL?

    Unless such a unique number is required, which it doesn't seem to be for the OP, I'd do neither. There's no point in adding unique numbers for the sake of adding unique numbers.

    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
  • One other thing Melanie, unless there's more to the code than you've shown, the temp table isn't needed at all. This should be more efficient

    SELECT '-1' AS EmpLogin, 'Please Select' AS AttorneyName

    UNION ALL

    SELECT RTRIM(LTRIM(EmpLogin)) + '|' + RIGHT(PhoneNumber,4), AS EmpLogin

    LastName + ', ' + FirstName AS AttorneyName

    FROM dbo.Employees

    WHERE JobCode BETWEEN 100 AND 500

    AND EmpTermDate IS NULL

    ORDER BY AttorneyName

    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
  • Assuming the "Please select" needs to be the first entry? then the ORDER BY needs to perhaps be:

    ORDER BY

    CASE WHEN EmpLogin = '-1' THEN 1 ELSE 2 END,

    AttorneyName

  • Kristen-173977 (10/9/2015)


    Assuming the "Please select" needs to be the first entry? then the ORDER BY needs to perhaps be:

    ORDER BY

    CASE WHEN EmpLogin = '-1' THEN 1 ELSE 2 END,

    AttorneyName

    Any even simpler approach is to change the first entry to '<Please Select>'. Angle brackets sort before alpha characters, and simultaneously draw attention to the fact that this in not a name.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Personally I'm not keen on that sort of workaround (or the "-1" for the EmpLogin for that matter) such things tend to have side effects and/or break sooner or later - someone's name starts with a space, or even a digit because of Goofy Data, or the QA lot complain that the HTML validation fails (although "& lt;" [EDIT can't figure out to escape that, hence the space] would sort even lower than "<" 😎 ). When I have to use a UNION for this type of "extra rows" I force the sort order:

    SELECT EmpLogin, AttorneyName

    FROM

    (

    SELECT 1 AS Seq, '-1' AS EmpLogin, 'Please Select' AS AttorneyName

    UNION ALL

    SELECT 2 AS Seq, RTRIM(LTRIM(EmpLogin)) + '|' + RIGHT(PhoneNumber,4), AS EmpLogin

    LastName + ', ' + FirstName AS AttorneyName

    FROM dbo.Employees

    WHERE JobCode BETWEEN 100 AND 500

    AND EmpTermDate IS NULL

    ) AS X

    ORDER BY Seq, AttorneyName

    but there is probably a smarter way to achieve the same end

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply