本文介绍了在一条语句中两次评估GETDATE -它会始终评估为相同吗?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

假设

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

其中的逻辑是,如果some_column为null,则此表达式应始终为true。但是,情况是否总是如此(因为两次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) ?

推荐答案

不,不安全。您正面临所谓的表达式,其中 GETDATE()是书架示例,这些表达式在查询启动时进行一次评估,然后使用缓存的评估值。但是,每个事件的发生都被分别评估一次,并且两个评估可以落在日期时间精度边界的不同侧,从而导致两个不同值。

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()在声明中两次将被评估两次

GETDATE() twice in a statement will be evaluate twice

这篇关于在一条语句中两次评估GETDATE -它会始终评估为相同吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-21 09:07