Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ) 
share|improve this question
    
How much faster? Do you see any difference in the query plans if you run them in SSMS and ask for that to be output? –  David Spillett May 7 '13 at 7:47
add comment

2 Answers

up vote 3 down vote accepted

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

share|improve this answer
1  
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
    
@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... –  Justicator May 7 '13 at 15:06
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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