哪个更好地比较日期<或DateDiff？ [英] Which is better to compare dates < or DateDiff?

问题描述

哪个更好？

select *
from TableA
where productDate < '12/9/2013'

或

select *
from TableA
where DATEDIFF(day, productDate, '12/9/2013') > 0

在阅读文章时，我读到使用Date函数（例如：datediff，dateadd）条款影响业绩负面。这是真的吗？

While skimming thru articles, I read that using Date functions (ex: datediff, dateadd) in where clauses affected performance negatively. Is this true?

推荐答案

最好的可能是：

SELECT <column list> -- not * (1)
  FROM dbo.TableA -- please always specify schema (2)
  WHERE productDate < '20131209'; -- always use a semi-colon (3)
    -- and always use a non-regional, unambiguous date format (4)

这将是最佳的原因是因为它使优化器在 productDate 列上使用索引的最佳机会。即使今天不存在一个索引，有人可能会在明天添加一个。将 c> DATEDIFF（）这样的函数应用于列将永远使表达式不可缩放，这意味着它将始终具有使用效率较低的扫描（假设这是唯一的搜索谓词）。

The reason this will be best is because it gives the optimizer the best chance to use an index on the productDate column. Even if an index doesn't exist today, someone may add one tomorrow. Applying functions like DATEDIFF() to the column will always make the expression non-sargable, meaning it will always have to use a less efficient scan (assuming that's the only search predicate).

对于内联评论：

1. http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/10/bad-habits-to-kick-using-select-omitting-the- column-list.aspx

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/11/bad-habits- to-kick-avoid-the-schema-prefix.aspx

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/09/03/ladies-and-gentlemen-start-your-semi-colons.aspx

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx

这篇关于哪个更好地比较日期<或DateDiff？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！