TSQL数据时间函数在where子句中 [英] TSQL datetimes functions in where clause

问题描述

我必须对在MSSQL服务器中托管的数据库的表进行查询。

I have to make a query to a table of a database that is hosted in a MSSQL server.

查询如下：

select *
from tableA
where createdOn between @startDate and @endDate

变量的类型@startDate而@endDate是datetime。如果@endDate等于当前日期，并且@startDate等于前一个月的日期，我认为我们可以将查询作为

The type of variables @startDate and @endDate is datetime. If @endDate is equal to the current date and @startDate is equal to the date once month before, I thought that we could make the query either as

declare @startDate datetime
declare @endDate datetime

set @startDate = DATEADD(month,-1,GETDATE())
set @endDate = GETDATE()


select *
from tableA
where createdOn between @startDate and @endDate

或作为

select *
from tableA
where createdOn between DATEADD(month,-1,GETDATE()) and GETDATE()

我以为第一个查询将比第二个查询更快，因为在第二个查询中，我们称之为where子句中的函数。我写了两个版本的我的查询，我也执行它们。不过，我并没有注意到这个表现有很大差异。

I thought that the first query would be faster that the second, because in the second query we call the functions in the where clause. I wrote both versions of my query and I executed them also. However, I didn't notice any significant difference in the performance.

有人可以解释一下为什么我没有看到任何改变。也许认为第一个查询应该更快，第二个是错误的。请让我知道，id是这样的，为什么是错的。

Could someone explain me why I didn't see any change. Maybe the thought that the first query should be faster that the second was false. Please let me know, id that's the case, why is wrong.

提前感谢任何帮助。

推荐答案

查询优化器。

查询优化器进行了两个查询一样。它知道GETDATE（）对于所有的测试是一样的，所以优化调用。

The query optimiser made both queries the same. It knows that GETDATE() is the same for all tests so optimises the call away.

阅读这里：查询优化器深度潜水和查询优化器，以获得比您想要的更多信息： - ）

Read here: Query Optimiser Deep Dive and Query Optimiser for way more info than you'd ever want :-)

这篇关于TSQL数据时间函数在where子句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！