Curious inconsistent behaviour from SQL Server in windowed function clauses?

Question

Whilst asking another question, I discovered that SQL Server (happens both in 2005 and 2008) seems to have strange inconsistent behaviour when dealing with CASE statements in the clauses of windowed functions. The following code gives an error:

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

The error is Windowed functions do not support constants as ORDER BY clause expressions. I presume this is because the case statement might evaluate to NULL, which is a constant. As might also be expected, this code gives the same error:

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

... presumably for the same reason. However, this code does not give an error:

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

The only difference here from the first codeblock is that I have moved one of the case statements' conditional operands into a variable, @asc. This now works fine. Why, though? The case statements may still evaluate to NULL, 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?

All this behvaiour can be checked by playing around with this query.

---

Update: This restriction doesn't just apply to OVER clauses (though they do give a different error) - it applies to all ORDER BY clauses since SQL Server 2005. Here's a query that also shows the restriction with a regular SELECT's ORDER BY clause.

Answer 1

Books online indicates that "A sort column can include an expression, but when the database is in SQL Server (90) compatibility mode, the expression cannot resolve to a constant." however it does not define "constant".

From thinking about it and some experimentation it seems clear that this means an expression for which a literal constant value can successfully be calculated at compile time.

/*Works - Constant at run time but SQL Server doesn't do variable sniffing*/
DECLARE @Foo int
SELECT ROW_NUMBER() OVER (ORDER BY @Foo) 
FROM master..spt_values 

/*Works - Constant folding not done for divide by zero*/
SELECT ROW_NUMBER() OVER (ORDER BY $/0) 
FROM master..spt_values 

/*Fails - Windowed functions do not support 
   constants as ORDER BY clause expressions.*/
SELECT ROW_NUMBER() OVER (ORDER BY $/1) 
FROM master..spt_values