Does SQL Server evaluate functions once for every row?

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:

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?

Certain functions that are known to be runtime constants go through the process called constant folding. When 'folding' a constant, an expression is evaluated early in the query execution, the result is cached and the cached result used 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 others, for instance NEWID(), are not foldable and will force an evaluation per row.

StackExchange Database Administrators Q#18459, answer score: 20