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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a query like this:

FROM   MyTable
    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
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
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


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.