在一个语句中两次评估GETDATE - 它总是评估为相同吗？

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) ?

其中逻辑是,如果some_column为null,则此表达式应始终为true。然而,这总是如此(因为在getdate()的两次评估之间已经过了一段时间并且它们不会相等)?

5 个解决方案

#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.

不,不安全。您正面临所谓的运行时常量表达式,其中GETDATE()是书架示例,它在查询启动时计算一次,随后使用缓存的计算值。但是,每次出现都会单独评估一次,并且两次评估可能会落在日期时间精度边界的不同侧,从而产生两个不同的值。

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

GETDATE()在语句中两次将被评估两次

#2

Since you are looking for true in the condition, you don't need to use getDate() twice. Just put in a very large date instead...

由于您在条件中寻找true,因此您不需要两次使用getDate()。只是放入一个非常大的日期......

For example:

isnull(some_column, '2999-01-01') >= getDate()

as in

declare @some_column(datetime)
select case when isnull(@some_column,'2999-01-01') >= getdate() then 1 else 0 end

which returns 1.

返回1。

Alternatively you can do it properly and check for the null explicitly:

或者,您可以正确地执行此操作并显式检查null:

(some_column >= getdate() or some_column is null)

#3

Since you are invoking GETDATE() twice, this may fail, though most of the time it will work right.

由于您要两次调用GETDATE(),这可能会失败,但大部分时间它都可以正常工作。

You can do the following to mitigate:

您可以执行以下操作来缓解:

DECLARE currentDate DATETIME

SELECT currentDate = GETDATE()

isnull(some_column, currentDate) >= currentDate

#4

In SQL Server 2000 and previous versions, getdate() is a deterministic function evaluated ONCE per SQL sentence. From 2005 and on, getdate is NOT deterministic, it's evaluated each time so you should assign the value to a variable.

在SQL Server 2000和以前的版本中,getdate()是一个按每个SQL语句评估ONCE的确定性函数。从2005年开始,getdate不是确定性的,每次都会对它进行评估,因此您应该将值赋给变量。

#5

Why do you want to use date. I mean there is no reason to ask sql server to evaluate/process for a default true condition. You can instead use

你为什么要使用日期。我的意思是没有理由要求sql server评估/处理默认的真实条件。你可以改用

isnull(some_column, 2) >= 1

#1