为每一行强制重新计算 GetDate()

2021-09-10 00:00:00 tsql sql-server getdate

在 SQL Server Management Studio 中运行此命令时

When running this command in SQL Server Management Studio 

UPDATE LogChange 
SET Created = GETDATE() 
WHERE id > 6000

奇怪的事情发生了.. :)

something strange happens.. :)

Created 列中的所有行都获得相同的值.

All rows get the same value in the Created column.

如何强制"SQL Server 重新计算每一行的 GetDate?

How do I "force" SQL Server to recalculate the GetDate for every row?

推荐答案

如果您想保证将单独的值应用于每一行，请编写一个绝对能做到这一点的查询:

If you want to guarantee that separate values are applied to each row, write a query that definitely does that:

declare @dt datetime
set @dt = GETDATE()

;With Diffs as (
    select
        *,
        ROW_NUMBER() OVER (ORDER BY id) * 10 as Offset
    from
        LogChange
    WHERE id > 6000
)
UPDATE Diffs 
SET Created = DATEADD(millisecond,Offset,@dt)

我们知道上面会为每一行生成单独的偏移量，并且我们肯定会将这些偏移量添加到一个固定值.经常观察到 GETDATE() 将为所有行返回相同的值，但我找不到任何保证这一点的特定文档，这就是为什么我还介绍了一个 @dt 变量，以便我知道它只分配一次.

We know that the above will generate separate offsets for each row, and that we're definitely adding those offsets to a fixed value. It has often been observed that GETDATE() will return the same value for all rows but I cannot find any specific documentation that guarantees this, which is why I've also introduced a @dt variable so that I know it's only assigned once.

相关文章