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


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()

2 Answers 2


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

SET @date = GETDATE()
SELECT T.DateCol, DATEADD(dd,-100,@date)
FROM dbo.TableName AS T
WHERE T.DateCol > DATEADD(dd,-100,@date)
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 :)

  • 2
    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. Commented May 7, 2013 at 14:42
  • @MartinSmith Thanks for the remark. runtime constant exists in the scope of a single query, but if the same code has several queries then 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
    Commented May 7, 2013 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.