title: Curious inconsistent behaviour from SQL Server in windowed function clauses?
body: <p>Whilst asking <a href="https://stackoverflow.com/questions/7203110/conditional-sort-order-in-sql-server-windowed-function-clauses">another question</a>, I discovered that SQL Server (happens both in 2005 and 2008) seems to have strange inconsistent behaviour when dealing with <code>CASE</code> statements in the clauses of windowed functions.  The following code gives an error:</p>
  <pre><code>declare @t table (SortColumn int)
  insert @t values (1), (2), (3)
  declare @asc bit
  set @asc = 0
  
  select  row_number() over (order by
              case when 1=1 then SortColumn end asc,
              case when 1=0 then SortColumn end desc) RowNumber
  ,       *
  from    @t
  </code></pre>
  <p>The error is <strong>Windowed functions do not support constants as ORDER BY clause expressions.</strong>  I presume this is because the <code>case</code> statement might evaluate to <code>NULL</code>, which is a constant.  As might also be expected, this code gives the same error:</p>
  <pre><code>declare @t table (SortColumn int)
  insert @t values (1), (2), (3)
  declare @asc bit
  set @asc = 0
  
  select  row_number() over (order by
              NULL asc,
              NULL desc) RowNumber
  ,       *
  from    @t
  </code></pre>
  <p>... presumably for the same reason.  However, this code does not give an error:</p>
  <pre><code>declare @t table (SortColumn int)
  insert @t values (1), (2), (3)
  declare @asc bit
  set @asc = 0
  
  select  row_number() over (order by
              case when @asc=1 then SortColumn end asc,
              case when @asc=0 then SortColumn end desc) RowNumber
  ,       *
  from    @t
  </code></pre>
  <p>The only difference here from the first codeblock is that I have moved one of the <code>case</code> statements' conditional operands into a variable, <code>@asc</code>.  This now works fine.  Why, though?  The <code>case</code> statements may still evaluate to <code>NULL</code>, which is a constant, so it shouldn't work... but it does.  Is this consistent somehow, or is it special case behaviour put in by Microsoft?</p>
  <p>All this behvaiour can be checked by playing around with <a href="https://data.stackexchange.com/stackoverflow/q/110520/" rel="nofollow noreferrer">this query</a>.</p>
  <hr />
  <p><strong>Update:</strong> This restriction doesn't just apply to <code>OVER</code> clauses (though they do give a different error) - it applies to all <code>ORDER BY</code> clauses since SQL Server 2005.  <a href="https://data.stackexchange.com/stackoverflow/q/110634/" rel="nofollow noreferrer">Here's a query</a> that also shows the restriction with a regular <code>SELECT</code>'s <code>ORDER BY</code> clause.</p>