SQL Server函数WIthin Case语句

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不是SARGable，但这在这里完全无关紧要，因为在这种情况下，SQL Server中的查询优化器不会考虑索引使用情况(而不是试图查找覆盖的可扫描路径)。据我所知，索引路径的DATEDIFF相关表达式的候选与这个问题完全无关。

很容易证明，一旦找到第一个真谓词，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它。请注意，原始问题指定了一个没有ORDER BY子句的TOP 10子句，因此我们实际得到的值是随机的。但是，如果我们在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‘。

如果我们去掉前10行，肯定会得到一些行，然后得到Divide by zero错误。因此，我们可以得出结论，SQL Server正在对CASE语句进行早期退出评估。

最重要的是，the documentation还告诉我们：

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

也许这个问题的目的是要问，公共DATEDIFF()子表达式是否从所有CASE语句中获得，计算一次，然后在每个谓词的上下文中求值。通过观察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