Puzzling ORDER BY

Dear SQLers:

http://www.mssqltips.com/sqlserverti...in-sql-server/
has an example with some puzzling code. Example 3's code is:

***** Start of Code *****
CREATE PROCEDURE dbo.getCustomerData @sortby VARCHAR(9),
@sortdirection CHAR(4)
AS
SET nocount ON

SELECT customerid, firstname, lastname, statecode, statedescription,
totalsales
FROM dbo.Customer
ORDER BY
CASE @sortdirection
WHEN 'asc' THEN
CASE @sortby
WHEN 'firstname' THEN firstname
WHEN 'lastname' THEN lastname
END
END
ASC,
CASE @sortdirection
WHEN 'desc' THEN
CASE @sortby
WHEN 'firstname' THEN firstname
WHEN 'lastname' THEN lastname
END
END
DESC
GO

EXEC dbo.getCustomerData 'lastname', 'desc'
***** End of Code *****

This looks like it specifies an order of two columns one
ascending and one descending but with one of them null. How does this
work? The syntax is weird (to me).

Sincerely,

Gene Wirchenko

On 2012-06-21 18:37, Gene Wirchenko wrote:
> Dear SQLers:
>
> http://www.mssqltips.com/sqlserverti...in-sql-server/
> has an example with some puzzling code. Example 3's code is:
>
> ***** Start of Code *****
> CREATE PROCEDURE dbo.getCustomerData @sortby VARCHAR(9),
> @sortdirection CHAR(4)
> AS
> SET nocount ON
>
> SELECT customerid, firstname, lastname, statecode, statedescription,
> totalsales
> FROM dbo.Customer
> ORDER BY
> CASE @sortdirection
> WHEN 'asc' THEN
> CASE @sortby
> WHEN 'firstname' THEN firstname
> WHEN 'lastname' THEN lastname
> END
> END
> ASC,
> CASE @sortdirection
> WHEN 'desc' THEN
> CASE @sortby
> WHEN 'firstname' THEN firstname
> WHEN 'lastname' THEN lastname
> END
> END
> DESC
> GO
>
> EXEC dbo.getCustomerData 'lastname', 'desc'
> ***** End of Code *****
>
> This looks like it specifies an order of two columns one
> ascending and one descending but with one of them null.

Yes. ORDER BY NULL effectively doesn't sort, because the same sort key is
applied to all rows. So for @sortby = 'lastname', @sortdirection = 'desc',
the ORDER BY becomes

ORDER BY NULL ASC, lastname DESC

With the first part having no effect on the end result.

> How does this work?

Not particularly well, since the optimizer doesn't know what order you
actually want until the query is executed. You may or may not get acceptable
performance out of it. I know the author explicitly wrote the article as a
way of illustrating how to get *away* from dynamic SQL, and certainly, if
you can get these queries to work well for you they're easier than cobbling
together ORDER BY clauses as text, but unfortunately dynamic SQL often
remains the best choice here where performance is concerned. The author
demonstrates the query on 4 rows, but neglects to demonstrate it on 4
million rows.

--
J.

On Thu, 21 Jun 2012 21:08:33 +0200, Jeroen Mostert
<jmostert@xs4all.nl> wrote:

>On 2012-06-21 18:37, Gene Wirchenko wrote:
>> Dear SQLers:
>>
>> http://www.mssqltips.com/sqlserverti...in-sql-server/
>> has an example with some puzzling code. Example 3's code is:
>>
>> ***** Start of Code *****
[snip]
>> ***** End of Code *****
>>
>> This looks like it specifies an order of two columns one
>> ascending and one descending but with one of them null.
>
>Yes. ORDER BY NULL effectively doesn't sort, because the same sort key is
>applied to all rows. So for @sortby = 'lastname', @sortdirection = 'desc',
>the ORDER BY becomes
>
> ORDER BY NULL ASC, lastname DESC
>
>With the first part having no effect on the end result.

What confused me is that explicitly using null (as you wrote) is
an error. This left me wondering about the syntax.

>> How does this work?
>
>Not particularly well, since the optimizer doesn't know what order you
>actually want until the query is executed. You may or may not get acceptable
>performance out of it. I know the author explicitly wrote the article as a
>way of illustrating how to get *away* from dynamic SQL, and certainly, if
>you can get these queries to work well for you they're easier than cobbling
>together ORDER BY clauses as text, but unfortunately dynamic SQL often
>remains the best choice here where performance is concerned. The author
>demonstrates the query on 4 rows, but neglects to demonstrate it on 4
>million rows.

It seems to me that each case would be executed, and in the case
of, say, 4 million rows, this could be quite a hit. Is that why you
are concerned about the performance?

While trying to figure it out, I found other examples with up to
six different case orders. It struck me as rather kludgy.

Sincerely,

Gene Wirchenko

Jeroen Mostert (jmostert@xs4all.nl) writes:
> Not particularly well, since the optimizer doesn't know what order you
> actually want until the query is executed. You may or may not get
> acceptable performance out of it. I know the author explicitly wrote the
> article as a way of illustrating how to get *away* from dynamic SQL, and
> certainly, if you can get these queries to work well for you they're
> easier than cobbling together ORDER BY clauses as text, but
> unfortunately dynamic SQL often remains the best choice here where
> performance is concerned. The author demonstrates the query on 4 rows,
> but neglects to demonstrate it on 4 million rows.

Well, if you have umpteen sort conditions, the odds are very good that the
very most of them will call for sorting anyway. But, true, if a sort
condition is used 80% of the time, and there is a index matching this
condition dynamic SQL wins.

Although, these days you can avoid the syntax overhead of dynamic SQL
by using OPTION (RECOMPILE). (Requires SQL 2008 SP2 or SQL 2008 R2 SP1.)
Then SQL Server will compile the statement every time, and consider
the parameters as constants.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On 2012-06-21 22:40, Gene Wirchenko wrote:
> On Thu, 21 Jun 2012 21:08:33 +0200, Jeroen Mostert
> <jmostert@xs4all.nl> wrote:
>
>> On 2012-06-21 18:37, Gene Wirchenko wrote:
>>> Dear SQLers:
>>>
>>> http://www.mssqltips.com/sqlserverti...in-sql-server/
>>> has an example with some puzzling code. Example 3's code is:
>>>
>>> ***** Start of Code *****
> [snip]
>>> ***** End of Code *****
>>>
>>> This looks like it specifies an order of two columns one
>>> ascending and one descending but with one of them null.
>>
>> Yes. ORDER BY NULL effectively doesn't sort, because the same sort key is
>> applied to all rows. So for @sortby = 'lastname', @sortdirection = 'desc',
>> the ORDER BY becomes
>>
>> ORDER BY NULL ASC, lastname DESC
>>
>> With the first part having no effect on the end result.
>
> What confused me is that explicitly using null (as you wrote) is
> an error. This left me wondering about the syntax.
>
Yes, you're right -- if you supply a constant expression explicitly, SQL
Server will catch this and flag it as nonsensical. A bit overzealous, I
think. It's mostly harmless, compared to other things that aren't detected.

If you use "NULL + [column]", or any other expression that always yields
NULL but isn't constant, the error goes away. The CASE, of course, is not a
constant expression.

>>> How does this work?
>>
>> Not particularly well, since the optimizer doesn't know what order you
>> actually want until the query is executed. You may or may not get acceptable
>> performance out of it. I know the author explicitly wrote the article as a
>> way of illustrating how to get *away* from dynamic SQL, and certainly, if
>> you can get these queries to work well for you they're easier than cobbling
>> together ORDER BY clauses as text, but unfortunately dynamic SQL often
>> remains the best choice here where performance is concerned. The author
>> demonstrates the query on 4 rows, but neglects to demonstrate it on 4
>> million rows.
>
> It seems to me that each case would be executed, and in the case
> of, say, 4 million rows, this could be quite a hit. Is that why you
> are concerned about the performance?
>
The problem is not the CASE itself (the execution overhead is trivial, and
constant per query) but the fact that the optimizer doesn't know what order
you actually want at compile time. If you write "ORDER BY lastname DESC" and
there is an index on lastname, the optimizer can simply use that. But if you
have multiple clauses covered by CASE, the optimizer cannot know what order
you want, so it won't be able to use any indexes.

As Erland pointed out, in the worst cases where you order by lots of stuff
it's unlikely an index can be proficiently used anyway, so this doesn't
always matter. However, his remark about OPTION (RECOMPILE) is not entirely
correct, I think -- I'll respond to that separately.

> While trying to figure it out, I found other examples with up to
> six different case orders. It struck me as rather kludgy.
>
Dynamic search or sort conditions can't really avoid looking kludgy -- the
only other major option is dynamic SQL, which looks even kludgier. Building
it client side can avoid some of the kludginess, but has its own drawbacks
in security and maintainability.

--
J.

On 2012-06-21 23:31, Erland Sommarskog wrote:
> Jeroen Mostert (jmostert@xs4all.nl) writes:
>> Not particularly well, since the optimizer doesn't know what order you
>> actually want until the query is executed. You may or may not get
>> acceptable performance out of it. I know the author explicitly wrote the
>> article as a way of illustrating how to get *away* from dynamic SQL, and
>> certainly, if you can get these queries to work well for you they're
>> easier than cobbling together ORDER BY clauses as text, but
>> unfortunately dynamic SQL often remains the best choice here where
>> performance is concerned. The author demonstrates the query on 4 rows,
>> but neglects to demonstrate it on 4 million rows.
>
> Well, if you have umpteen sort conditions, the odds are very good that the
> very most of them will call for sorting anyway. But, true, if a sort
> condition is used 80% of the time, and there is a index matching this
> condition dynamic SQL wins.
>
> Although, these days you can avoid the syntax overhead of dynamic SQL
> by using OPTION (RECOMPILE). (Requires SQL 2008 SP2 or SQL 2008 R2 SP1.)
> Then SQL Server will compile the statement every time, and consider
> the parameters as constants.
>
I was about to dispute your claim to say the optimizer wasn't actually
clever enough to do this particular optimization even with OPTION
(RECOMPILE), but I'm wrong -- it is. It took me a while to find a server
that had the required version -- turns out we still have plenty that are
running 2008 SP1, and of course RECOMPILE isn't good enough on those versions.

So when you've got a working RECOMPILE, this approach can be just as
efficient as dynamic SQL.

--
J.

Jeroen Mostert (jmostert@xs4all.nl) writes:
> Yes, you're right -- if you supply a constant expression explicitly, SQL
> Server will catch this and flag it as nonsensical. A bit overzealous, I
> think. It's mostly harmless, compared to other things that aren't
> detected.
>
> If you use "NULL + [column]", or any other expression that always yields
> NULL but isn't constant, the error goes away. The CASE, of course, is
> not a constant expression.

Or you can say ORDER BY (SELECT NULL). Which is not very useful in the
main ORDER BY for a query, but this is useful with row_number() when you
don't care about the order. (ORDER BY is mandatory for row_number.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Thursday, June 21, 2012 5:37:54 PM UTC+1, Gene Wirchenko wrote:
> Dear SQLers:
>
> http://www.mssqltips.com/sqlserverti...in-sql-server/
> has an example with some puzzling code. Example 3's code is:
>
> ***** Start of Code *****
> CREATE PROCEDURE dbo.getCustomerData @sortby VARCHAR(9),
> @sortdirection CHAR(4)
> AS
> SET nocount ON
>
> SELECT customerid, firstname, lastname, statecode, statedescription,
> totalsales
> FROM dbo.Customer
> ORDER BY
> CASE @sortdirection
> WHEN 'asc' THEN
> CASE @sortby
> WHEN 'firstname' THEN firstname
> WHEN 'lastname' THEN lastname
> END
> END
> ASC,
> CASE @sortdirection
> WHEN 'desc' THEN
> CASE @sortby
> WHEN 'firstname' THEN firstname
> WHEN 'lastname' THEN lastname
> END
> END
> DESC
> GO
>
> EXEC dbo.getCustomerData 'lastname', 'desc'
> ***** End of Code *****
>
> This looks like it specifies an order of two columns one
> ascending and one descending but with one of them null. How does this
> work? The syntax is weird (to me).
>
> Sincerely,
>
> Gene Wirchenko

Has the question been answered?

The code is a means of writing a single
SQL statement that can operate as
"ORDER BY firstname ASC" or "BY lastname ASC"
or "BY firstname DESC" or "BY lastname DESC",
but everyone thinks it's going to run like
a dead Galapagos turtle in real life, if
SQL Server can't decipher the logic and
split out the actual meaningful columns in
the formula - and we seem to assume that
it can't.

Of course you can "ORDER BY columnWhichIsNull"
which will be random order unless you add more
columns to the ORDER clause.

There are cases where "dynamic SQL" isn't an option,
such as in functions, or you may just not /like/
the stuff.

You could write several different versions of the
entire SELECT statement, and switch to using
whichever one is required - but the more variations
that you want to have, the more distinct SQL
statements would be needed - "geometrically".

You can put ugly statement logic into a view -
but SQL Server is still going to have to execute
that logic in the query (unless it's an
indexed view). In my organisation, we've got
some horrible views, and views on views, and then
they wonder why this query runs like a dead
giant turtle...

(In the original example, you could JOIN to a
table of all the combinations of values of
"sortby" and "sortdirection", but there's no
reason to, except to prove that you can.)