SQL Server Function WIthin Case Statement

Question

    select top 10 *, case     
    when datediff(day,DateOfSale, getDate())  > 5 then '5'
        when datediff(day,DateOfSale, getDate())  > 10 then '10'
... (more datediff clauses)
...
...
        else '20'
        end as jack
        from Foo

SQL Server是否足够聪明，可以在case语句中评估datediff函数调用一次，然后为每个when子句使用该值?还是函数被调用n次，其中n是when子句的个数?

Answer 1

很难看出SQL Server如何计算一次调用。调用有一个列作为参数，因此必须对每一行求值。

因此，您的条件最好写成:

when DateOfSale < dateadd(day, -5, getdate()) then '5'

在这种情况下，差异很小。日期计算很便宜。

函数调用起作用的经典示例是在日期列上有索引的表上的where条件。例如，在(dt)上有索引的YourTable。这个查询将允许使用索引:

select * from YourTable where dt < dateadd(day, -5, getdate())

而这个查询不会:

select * from YourTable where datediff(day, DateOfSale, getDate()) > 5

Answer 2

令人费解的是，这么多的答案都提到了索引。事实上，DATEDIFF不是可扫描的，但这在这里完全无关紧要，因为CASE WHEN不会导致SQL Server中的查询优化器考虑索引使用情况(而不是试图找到覆盖可扫描的路径)。据我所知，涉及dateff的表达式是否适合用于索引路径与这个问题完全无关。

很容易证明，一旦发现第一个真谓词，SQL Server确实会停止计算CASE语句中的谓词。

为了演示这一事实，让我们制作一些示例数据:

CREATE TABLE Diffy (SomeKey INTEGER NOT NULL IDENTITY(1,1), DateOfSale DATE);
DECLARE @ThisOne AS DATE;
SET @ThisONe = '2012-01-01';
WHILE @thisONe < '2013-01-01'
BEGIN
    INSERT INTO Diffy (DateOfSale) VALUES(@ThisOne);
    SET @ThisOne = DateAdd(d, 1, @ThisOne);
END;

然后，让我们按照原题的模式进行SELECT。注意，原来的问题指定了TOP 10子句而没有ORDER BY子句，因此我们实际得到的值是随机的。但是，如果我们在CASE中添加一个会毒害求值的子句，我们可以看到发生了什么:

SELECT TOP 10 *, CASE 
WHEN datediff(day,DateOfSale, getDate())  > 5 then '5'
WHEN datediff(day,DateOfSale, getDate())  > 10 then '10'
WHEN 1/0  > 1then 'boom'
ELSE '20' END
AS Jack
FROM Diffy;

注意，如果我们计算1/0 > 1，那么我们期望得到类似'Divide by zero error encountered.'的结果。但是，在我的服务器上运行这个查询会产生10行，Jack列中都有'5'。

如果我们拿走TOP 10，我们肯定会得到一些行，然后得到Divide by zero错误。因此，我们可以有把握地得出结论，SQL Server正在对CASE语句进行提前退出评估。

除此之外，文档还告诉我们:

CASE语句按顺序计算其条件，并在满足条件的第一个条件处停止。

也许这个问题是想问是否从所有CASE语句中提升公共DATEDIFF()子表达式，计算一次，然后在每个谓词的上下文中求值。通过观察SET SHOWPLAN_TEXT ON的输出，我认为我们可以得出结论，情况并非如此:

   |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN datediff(day,CONVERT_IMPLICIT(datetimeoffset(7),[Scratch3].[dbo].[Diffy].[DateOfSale],0),CONVERT_IMPLICIT(datetimeoffset(3),getdate(),0))>(5) THEN '5'  ELSE CASE WHEN datediff(day,CONVERT_IMPLICIT(datetimeoffset(7),[Scratch3].[dbo].[Diffy].[DateOfSale],0),CONVERT_IMPLICIT(datetimeoffset(3),getdate(),0))>(10) THEN '10' ELSE CASE WHEN (1)/(0)>(1)  THEN 'boom' ELSE '20' END END END))
        |--Index Scan(OBJECT:([Scratch3].[dbo].[Diffy].[DiffyHelper]))

由此，我们可以得出结论，该查询的结构意味着对每一行和每个谓词计算DATEDIFF()，因此O(rows * predicates)调用是最坏的情况。这将导致查询的一些CPU负载，但是DATEDIFF()不是非常那么昂贵，不应该太担心。如果在实践中，它最终导致了性能问题，那么有一些方法可以手动从查询中提升计算。例如，比较中日期相关的DATEDIFF()

Answer 3

当然，但不是在您的情况下(表达式基于每一行更改的表列值)，但在任何情况下，不要对表列值执行datediff，而是对谓词(比较)值运行dateadd，以便您的查询仍然可以使用DateOfSale上的任何现有索引…

  select top 10 *, 
      case When DateOfSale < dateadd(day, -20, getDate()) then '20'
           When DateOfSale < dateadd(day, -15, getDate()) then '15'
           When DateOfSale < dateadd(day, -10, getDate()) then '10' 
           When DateOfSale < dateadd(day, -5, getDate()) then '5' 
          else '20' end jack
  from Foo