在SQL Server中，一整天筛选项目的最佳方法是什么 [英] In SQL Server, what is the best way to filter items for an entire day

问题描述

在SQL Server中，我想显示特定日期的所有项目.最佳方法是:DATEPART，BETWEEN或> = +< ?所有这3个功能均应正常工作，但其中有一些性能更好/更差吗?

In SQL Server, I want to show all items for a particular day. What is the best way to do this: DATEPART, BETWEEN, OR >= + < ? All 3 should work functionally, but are some of them better/worse for performance?

1)

SELECT *
FROM BlogPosts
WHERE DATEPART(yyyy,BlogPostDate) = 2011
    AND DATEPART(m,BlogPostDate) = 5
    AND DATEPART(d,BlogPostDate) = 7

2)

SELECT *
FROM BlogPosts
WHERE BlogPostDate BETWEEN '2011-05-07' AND '2011-05-07 23:59:59'

3)

  SELECT *
    FROM BlogPosts
    WHERE BlogPostDate >= '2011-05-07' 
        AND BlogPostDate < '2011-05-08'

推荐答案

您提供的选项之外.数字3既可吹烟又会工作.

Out of the options you have presented. Number 3 as it is both sargable and will work.

数字1不可靠(不能使用索引).

Number 1 is not sargable (can't use an index).

数字2将错过有效的日期时间，例如2011-05-07 23:59:59.997(并且您在此处必须使用的确切最大日期时间将在smalldatetime，datetime和datetime2(x)之间变化)

Number 2 will miss valid datetimes such as 2011-05-07 23:59:59.997 (and the exact maximum datetime that you would have to use here would vary between smalldatetime, datetime, and datetime2(x))

如果您使用的是SQL Server 2008，则尽管外观为 ，但仍然可以选择

If you are on SQL Server 2008 an alternative option which is sargable despite appearances is

 SELECT *     
 FROM BlogPosts     
 WHERE CAST(BlogPostDate AS Date)  = '2011-05-07'

但是，我仍然会选择>= .... <作为您的数字3，因为它在所寻求的范围方面更加高效，而且还可能更好地利用列统计信息. 此处有更多详细信息.

However I would still opt for your Number 3 with the >= .... < as being more efficient both in terms of the range seeked and also potentially making better use of column statistics. More details of that here.

这篇关于在SQL Server中，一整天筛选项目的最佳方法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！