这个日期比较条件在SQL中是否可以通过SARG支持？ [英] Is this date comparison condition SARG-able in SQL?

问题描述

此条件是否可预测？

  AND DATEDIFF（month，p.PlayerStatusLastTransitionDate，@ now）在1和7之间
  

我的经验法则是左边的函数使条件不可维持..但是在某些地方，我已经阅读到BETWEEN子句是可修改的。
那么有人肯定知道吗？

供参考：

• 什么使SQL语句可存储？


• http://en.wikipedia.org/wiki/Sargable

注意：如果任何一位专家到此结束，请更新Sargable Wikipedia页面。我对其进行了一点更新，但是我确信它可以进一步改进：）

解决方案

使用AdventureWorks，如果我们看一下这些的话两个等价查询：

  SELECT OrderDate FROM Sales.SalesOrderHeader 
 WHERE DATEDIFF（month，OrderDate，GETDATE（））之间1和7; 
 
从Sales.SalesOrderHeader 
中选择OrderDate WHERE OrderDate> = DATEADD（MONTH，-7，GETDATE（））
 AND OrderDate< = DATEADD（MONTH，-1， GETDATE（））; 
  

在两种情况下，我们都会看到聚集索引扫描：

但是请注意仅在后一个查询上的推荐/缺失索引，因为它是唯一可以从中受益的索引：

如果我们向OrderDate列添加索引，然后再次运行查询：

 在Sales.SalesOrderHeader（OrderDate）上创建索引
 GO 
 
从Sales.SalesOrderHeader 
中选择OrderDate WHERE DATEDIFF（month，OrderDate，GETDATE（））在1和7之间； 
 
从Sales.SalesOrderHeader 
中选择OrderDate WHERE OrderDate> = DATEADD（MONTH，-7，GETDATE（））
 AND OrderDate< = DATEADD（MONTH，-1， GETDATE（））; 
  

我们看到了很大的不同-后者使用了搜索：

也请注意估算值与您的查询版本相去甚远。在大型数据集上，这绝对是灾难性的。

在极少数情况下，应用于该列的函数或其他表达式是可保存的。我知道的一种情况是 CONVERT（DATE，datetime_column）-但是该特定优化未记录在案，我还是建议不要这样做。不仅是因为您暗示要对列使用函数/表达式是可以的（并非在其他所有情况下），还因为
可能导致阅读浪费和灾难性的估计。

Is this condition sargable?

AND  DATEDIFF(month,p.PlayerStatusLastTransitionDate,@now) BETWEEN 1 AND 7)

My rule of thumb is that a function on the left makes condition non sargable.. but in some places I have read that BETWEEN clause is sargable. So does any one know for sure?

For reference:

• What makes a SQL statement sargable?
• http://en.wikipedia.org/wiki/Sargable

NOTE: If any guru ends here, please do update Sargable Wikipedia page. I updated it a little bit but I am sure it can be improved more :)

解决方案

Using AdventureWorks, if we look at these two equivalent queries:

SELECT OrderDate FROM Sales.SalesOrderHeader
WHERE DATEDIFF(month,OrderDate,GETDATE()) BETWEEN 1 AND 7;

SELECT OrderDate FROM Sales.SalesOrderHeader
WHERE OrderDate >= DATEADD(MONTH, -7, GETDATE())
  AND OrderDate <= DATEADD(MONTH, -1, GETDATE());

In both cases we see a clustered index scan:

But notice the recommended/missing index only on the latter query, since it's the only one that could benefit from it:

If we add an index to the OrderDate column, then run the queries again:

CREATE INDEX dt ON Sales.SalesOrderHeader(OrderDate);
GO

SELECT OrderDate FROM Sales.SalesOrderHeader
WHERE DATEDIFF(month,OrderDate,GETDATE()) BETWEEN 1 AND 7;

SELECT OrderDate FROM Sales.SalesOrderHeader
WHERE OrderDate >= DATEADD(MONTH, -7, GETDATE())
  AND OrderDate <= DATEADD(MONTH, -1, GETDATE());

We see much difference - the latter uses a seek:

Notice too how the estimates are way off for your version of the query. This can be absolutely disastrous on a large data set.

There are very few cases where a function or other expression applied to the column will be sargable. One case I know of is CONVERT(DATE, datetime_column) - but that particular optimization is undocumented, and I recommend staying away from it anyway. Not only because you'd be implicitly suggesting that using functions/expressions against columns is okay (it's not in every other scenario), but also because it can lead to wasted reads and disastrous estimates.

这篇关于这个日期比较条件在SQL中是否可以通过SARG支持？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！