Does SQL Server evaluate functions once for every row?

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.

Execution plans are great but sometimes they just don't tell you the truth. So here is a proof based on performance test.

(and the bottom line - the expression is not being evaluated for every row)

---

;with t(i) as (select 0 union all select i+1 from t where i < 9)
select getdate()-1 as col1,getdate() as col2,getdate() as col3 
into #t 
from t t0,t t1,t t2,t t3,t t4,t t5,t t6,t t7

(100000000 row(s) affected)

This is the OP query and it takes around 12 seconds to run

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

This query which stores the date in a parameter before the execution, takes about the same time, 12 seconds.

declare @dt datetime = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) 

SELECT col1
FROM   #t
WHERE  
      @dt
       BETWEEN col2 
       AND     col3
;

And just to verify the results -
This query which does the computation on col1 and therefore have to recalculate the expression for every row takes about 30 seconds to run.

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

---

All queries were executed repeatedly showing about the same metrics