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 )
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:
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 :)
GETDATE is a runtime constant and will not in fact be called many times. In the case of the OP as they are using the expression intermingled with a column reference I'm not sure it will actually effect the cardinality estimates.
– Martin Smith
May 7 '13 at 14:42
GETDATE will be called several times. however, it will probably not cost more then constant scan of the variable (same amount of times). as for their use - We can't be sure without their entire server :) I will not change my answer because the bottom line remains the same...
– Roi Gavish
May 7 '13 at 15:06
Most importantly, the main reason why you need to put it in a variable is if you want the exact same value (GETDATE() at that exact time) to be used elsewhere on the code. Otherwise, the performance difference of it is really a moot point.
