Sql-server – difference in performance between @date and getdate()

performancequery-performancesql serversql-server-2008

Usually I use the getdate() function in my where clauses to go back in time.
Something like:

 DOC.DATUM >= DATEADD(DD,-1*SSN_SDO.DANA_ZA_POVRAT,GETDATE())

Will SQL Server 2008R2 perform faster queries if I first declare a date parameter and use that in my queries instead?

declare @dateNow date = getdate()
...
where
     DOC.DATUM >= DATEADD(DD,-1*SSN_SDO.DANA_ZA_POVRAT,@dateNow )

Best Answer

The answer is - you have to test it to find out.
I did a test of my own on a table which has ~8,000,000 rows

DECLARE @date DATETIME
SET @date = GETDATE()
;
SELECT T.DateCol, DATEADD(dd,-100,@date)
FROM dbo.TableName AS T
WHERE T.DateCol > DATEADD(dd,-100,@date)
;
SELECT T.DateCol, DATEADD(dd,-100,GETDATE())
FROM dbo.TableName AS T
WHERE T.DateCol > DATEADD(dd,-100,GETDATE())
;

In my case the use of the variable caused SQL Server to estimate an expensive plan:

enter image description here
the reason is that SQL Server builds the query plan during compilation,
at that time the value of the variable is not yet known and SQL Server can not use the statistics.
I suggest you read about parameterization

There are other cases where the use of the variable will cost less then calling a function many times,
so here it is again - TEST TEST, and TEST :)

Related Question