October 8, 2015 at 2:20 pm
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?
October 8, 2015 at 2:43 pm
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
October 8, 2015 at 2:44 pm
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
October 8, 2015 at 2:46 pm
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
October 8, 2015 at 3:05 pm
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.
October 8, 2015 at 3:21 pm
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
October 9, 2015 at 2:49 am
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
...
October 9, 2015 at 2:52 am
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
October 9, 2015 at 3:26 am
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
October 9, 2015 at 3:28 am
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?
October 9, 2015 at 3:34 am
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
October 9, 2015 at 3:39 am
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
October 9, 2015 at 3:55 am
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
October 9, 2015 at 7:51 am
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
October 9, 2015 at 9:01 am
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