在 T-SQL 中使用 DATEDIFF

Question

我在 SQL 语句中使用 DATEDIFF。 我正在选择它，并且我还需要在 WHERE 子句中使用它。 这个语句不起作用......

SELECT DATEDIFF(ss, BegTime, EndTime) AS InitialSave
FROM MyTable
WHERE InitialSave <= 10

它给出了消息：无效的列名“InitialSave”

但是这个语句工作正常......

SELECT DATEDIFF(ss, BegTime, EndTime) AS InitialSave
FROM MyTable
WHERE DATEDIFF(ss, BegTime, EndTime) <= 10

我的程序员说这是低效的（似乎我正在调用函数两次）。

那么两个问题。 为什么第一个语句不起作用？ 使用第二个语句执行此操作是否效率低下？

Answer 1

除了使其“工作”之外，您还需要使用索引

使用带有索引的计算列或带有索引的视图，否则您将进行表扫描。 当你获得足够多的行时，你会感受到慢速扫描的痛苦！

计算列和 索引：

ALTER TABLE MyTable ADD
    ComputedDate  AS DATEDIFF(ss,BegTime, EndTime)
GO
CREATE NONCLUSTERED INDEX IX_MyTable_ComputedDate  ON MyTable 
    (
    ComputedDate
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

创建视图& 指数：

CREATE VIEW YourNewView
AS
SELECT
    KeyValues
        ,DATEDIFF(ss, BegTime, EndTime) AS InitialSave
    FROM MyTable
GO
CREATE CLUSTERED INDEX IX_YourNewView
    ON YourNewView(InitialSave)
GO

Answer 2

您必须使用该函数而不是列别名 - 它与 count(*) 等相同。PITA。

Answer 3

作为替代方案，您可以使用计算列。

Answer 4

注意：当我最初写这个答案时，我说过其中一列上的索引可以创建一个比其他答案性能更好的查询（并提到了 Dan Fuller 的答案）。 然而，我的想法并不是100%正确。 事实是，如果没有计算列或索引（物化）视图，则需要进行全表扫描，因为要比较的两个日期列来自相同的日期列桌子！

我相信下面的信息仍然有价值，即1）在正确的情况下提高性能的可能性，例如在不同表的列之间进行比较时，2）促进SQL开发人员遵循最佳实践和重塑的习惯他们的思维方向是正确的。

使条件可控制

我指的最佳实践是将一列移动到比较运算符的一侧，如下所示：

SELECT InitialSave = DateDiff(second, T.BegTime, T.EndTime)
FROM dbo.MyTable T
WHERE T.EndTime <= T.BegTime + '00:00:10'

正如我所说，这不会避免对但是，在这种情况下，单个表可能会产生巨大的差异：

SELECT InitialSave = DateDiff(second, T.BegTime, T.EndTime)
FROM
   dbo.BeginTime B
   INNER JOIN dbo.EndTime E
      ON B.BeginTime <= E.EndTime
      AND B.BeginTime + '00:00:10' > E.EndTime

在这两种情况下，EndTime 现在都单独位于比较的一侧。 假设 BeginTime 表的行数少得多，并且 EndTime 表在 EndTime 列上有索引，这将比使用 DateDiff(second, B.BeginTime, E.EndTime) 的任何内容。 现在它是sargable，这意味着有一个有效的“搜索参数”——因此当引擎扫描BeginTime表时，它可以< em>查找到 EndTime 表中。 需要仔细选择哪一列本身位于运算符的一侧 - 通过进行一些代数操作以切换到 AND B.BeginTime >，将 BeginTime 单独放置，这可能是值得尝试的; E.EndTime - '00:00:10'

DateDiff 的精度

我还应该指出 DateDiff 不会返回 elapsed时间，而是计算跨越的边界数量。 如果使用秒调用 DateDiff 返回 1，这可能意味着 3 毫秒 经过的时间，也可能意味着 1997 毫秒代码>! 这本质上是 +- 1 时间单位的精度。 为了获得 +- 1/2 时间单位的更好精度，您需要使用以下查询来比较 0 与 EndTime - BegTime：

SELECT DateDiff(second, 0, EndTime - BegTime) AS InitialSave
FROM MyTable
WHERE EndTime <= BegTime + '00:00:10'

现在最大舍入误差仅为 1第二个总数，而不是两个（实际上是一个 Floor() 操作）。 请注意，您只能减去 datetime 数据类型 - 要减去 date 或 time 值，您必须将其转换为 datetime 或使用其他方法来获得更好的精度（大量 DateAdd、DateDiff 以及可能的其他垃圾，或者可能使用更高精度的时间单位和划分）。

在计算小时、天或月等较大单位时，这一原则尤其重要。 1 个月 的 DateDiff 可能相隔 62 天（想想 2013 年 7 月 1 日 - 2013 年 8 月 31 日）！

Answer 5

您无法在 where 语句中访问 select 语句中定义的列，因为它们只有在执行了 where 后才会生成。

但是，您可以执行此操作

select InitialSave from 
(SELECT DATEDIFF(ss, BegTime, EndTime) AS InitialSave
FROM MyTable) aTable
WHERE InitialSave <= 10

作为旁注 - 这实际上将 DATEDIFF 根据其首次定义的位置移动到 where 语句中。 在 where 语句中的列上使用函数会导致索引无法有效使用，如果可能的话应该避免，但是如果您必须使用 datediff 那么您就必须这样做！