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.

I have a query like this:

SELECT col1
FROM   MyTable
WHERE  
    DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) 
       BETWEEN col2 
       AND     col3
;

This gives a tooltip on the execution plan similar to this:

Execution Tooltip

Does the dateadd part of the seek predicates get executed for every row in the query? Or does SQL Server calculate the value once for the whole query?

share|improve this question

1 Answer 1

up vote 8 down vote accepted

Certain functions that are known to be runtime constants go through the process called constant folding. By 'folding' a constant an expression is evaluated early in the query execution, the result is cached and the cached result instead when needed. The expression in your query DATEADD(dd, 0, DATEDIFF(dd, 0, getdate())) is, afaik, a runtime constant and thus will be folded and evaluated only once per query.

As trivia: the RAND() function that one would expect to be unfoldable is actually foldable, which leads to some unexpected behavior. But other, for instance NEWID(), are not foldable and will force an evaluation per row.

share|improve this answer
    
Thanks for the link and explanations :) –  Stuart Blackler May 28 '12 at 11:21
2  
You have to be really careful if you plan to rely on the "per-row evaluation" behaviour. –  Jon Seigel May 29 '12 at 2:22
    
@StuartBlackler - Here's a demonstration of how SQL Server folds functions like GETDATE(). –  Nick Chammas May 29 '12 at 15:23

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.