Evaluating GETDATE twice in a statement - will it always evaluate to be the same?

Question

suppose

isnull(some_column, getdate()) >= getdate()

where logic is if some_column is null this expression should always be true. However will this always be so (since between two evaluations of getdate() some time has passed and they won't be equal) ?

Answer 1

No, is not safe. You are facing so called runtime constants expressions, of which GETDATE() is the bookcase example, which are evaluate once at query startup and the subsequently the cached evaluated value is used. However each occurence is evaluated separately once and the two evaluation can fall on the separate sides of the datetime precision boundary, resulting in two different values.

A simple test reveals how this happens:

declare @cnt int = 0, @i int = 0
while @cnt = 0 
begin
    select @cnt = count(*)
    from master..spt_values 
    where getdate() != getdate();
    set @i += 1;
    if @cnt != 0
        raiserror(N'@cnt = %d this shoudl not happen but it dit after @i = %d', 16, 1, @cnt, @i);
end

In my case this was hit right away:

Msg 50000, Level 16, State 1, Line 9
@cnt = 2515 this shoudl not happen but it dit after @i = 694

I'm not addressing the question how to better do this (you already got plenty of advice) but the underlying question whether your assumption about the run-time execution is right (is not):

GETDATE() twice in a statement will be evaluate twice