Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1142

Re: Puzzling ORDER BY

Cross-posted to 2 groups.

Show all headers | View raw

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/sqlservertip/1455/using-the-case-expression-instead-of-dynamic-sql-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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Puzzling ORDER BY Gene Wirchenko <genew@ocis.net> - 2012-06-21 09:37 -0700
  Re: Puzzling ORDER BY Jeroen Mostert <jmostert@xs4all.nl> - 2012-06-21 21:08 +0200
    Re: Puzzling ORDER BY Gene Wirchenko <genew@ocis.net> - 2012-06-21 13:40 -0700
      Re: Puzzling ORDER BY Jeroen Mostert <jmostert@xs4all.nl> - 2012-06-22 08:24 +0200
        Re: Puzzling ORDER BY Erland Sommarskog <esquel@sommarskog.se> - 2012-06-22 11:31 +0200
    Re: Puzzling ORDER BY Erland Sommarskog <esquel@sommarskog.se> - 2012-06-21 23:31 +0200
      Re: Puzzling ORDER BY Jeroen Mostert <jmostert@xs4all.nl> - 2012-06-22 08:46 +0200
  Re: Puzzling ORDER BY rja.carnegie@gmail.com - 2012-07-02 03:36 -0700

csiph-web