Force recalculation of GetDate() for each row

Question

When running this command in SQL Server Management Studio

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

something strange happens.. :)

All rows get the same value in the Created column.

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

Answer 1

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)

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.

Answer 2

I have found the following “getdate()” pattern extremely useful over the years. Especially when updating multiple tables in the same transaction. Having the time values exactly match had more benefit to me then being accurate to the millisecond on individual rows.

declare @dtNow datetime 

SET @dtNow = GETDATE()

UPDATE LogChange 
SET Created = @dtNow
WHERE id > 6000

Answer 3

I don't think there is a way to do them in a single query because the operations in the same clause of a query are evaluated all-at-once.

Answer 4

A cursor solved it

declare @id int;

Declare toLoop  cursor for 
select Id from LogChange where id > 6000

 OPEN toLoop
 FETCH NEXT FROM toLoop INTO @id  

WHILE @@FETCH_STATUS = 0  
BEGIN  

    Update LogChange set Created = GETDATE() where id = @id

    FETCH NEXT FROM toLoop INTO @id  
END  

close toLoop
DEALLOCATE toLoop