来自 SQL Server 的窗口函数子句中奇怪的不一致行为? [英] Curious inconsistent behaviour from SQL Server in windowed function clauses?

问题描述

在询问另一个问题时，我发现在处理窗口函数子句中的 CASE 语句时，SQL Server(发生在 2005 和 2008 年)似乎有奇怪的不一致行为.以下代码报错:

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

错误是窗口函数不支持常量作为 ORDER BY 子句表达式. 我认为这是因为 case 语句可能评估为 NULL，这是一个常数.正如预期的那样，此代码给出了相同的错误:

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

这里与第一个代码块的唯一区别是我将一个 case 语句的条件操作数移动到一个变量 @asc 中.这现在工作正常.为什么呢?case 语句可能仍然计算为 NULL，这是一个常量，所以它不应该工作......但它确实如此.这是一致的，还是微软提出的特殊情况?

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.

更新:此限制不仅适用于 OVER 子句(尽管它们确实给出了不同的错误) - 它适用于所有 ORDER BY 子句自 SQL Server 2005 起.这是一个查询，也显示了限制常规 SELECT 的 ORDER BY 子句.

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.

推荐答案

联机丛书指出一个排序列可以包含一个表达式，但是当数据库处于 SQL Server (90) 兼容模式时，该表达式无法解析为持续的."但是它没有定义常量".

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 

这篇关于来自 SQL Server 的窗口函数子句中奇怪的不一致行为?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！