Curious inconsistent SQL Server behavior in occluded functions?

Question

While asking another question , I found that SQL Server (both in 2005 and 2008) seems to have strange inconsistent behavior when working with CASE statements in window function articles. 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 

Error Terminal functions do not support constants in ORDER BY clause expressions. I assume this is because the CASE statement can evaluate to NULL , which is a constant. As you might expect, 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 code block is that I transferred one of the conditional operands of the CASE statements to the @asc variable. Now it works great. But why? CASE statements can still evaluate to NULL , which is a constant, so it shouldn't work ... but it does. Is it consistent in some way, or is it a special case of Microsoft's behavior?

All of this can be verified with this query .

---

Update: This restriction does not only apply to OVER clauses (although they give a different error) - this applies to all ORDER BY clauses with SQL Server 2005. Here is a query that also shows a constraint with a regular SELECT ORDER BY .

Answer 1

Online books indicate that "the sort column may contain an expression, but when the database is in SQL Server compatibility mode (90), the expression cannot resolve the constant." however, it does not define a "constant."

From thinking about this and some experiments, it seems obvious that this means an expression for which a literal constant value can be successfully 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 

Answer 2

The ratings in your first example will never change.

You are comparing a constant with a constant that will constantly lead to a constant.

1=1 will always be TRUE .
1=0 will always be FALSE .