14

I have a trigger that automatically sets the CreationDate and ModifiedDate of the given entry to the current UTC time whenever a value is entered. (CreationDate will remain the same thereafter, while ModifiedDate will be updated on each update via another trigger).

I want to make sure that inserted-and-never-updated items will have exactly the same value for CreationDate and ModifiedDate, so I've used a variable like this:

DECLARE @currentTime DATETIME
SELECT @currentTime = GETUTCDATE()
UPDATE dbo.MyTable SET CreationDate = @currentTime, ModifiedDate = @currentTime
    ...

In my imperative-programming mentality, I am assuming this prevents GETUTCDATE() from being called twice, and potentially producing slightly different results. Is this actually necessary? If not, would this be more expensive, less expensive, or exactly the same as the following code?

UPDATE dbo.MyTable SET CreationDate = GETUTCDATE(), ModifiedDate = GETUTCDATE()
    ...
StriplingWarrior
  • 151,543
  • 27
  • 246
  • 315

5 Answers5

13

Thanks to the links provided by gbn, I believe this answers my question:

In common with rand() It is evaluated once per column but once evaluated remains the same for all rows. ... look at the ComputeScalar operator properties in the actual execution plan you will see that GetDate() is evaluated twice.

I checked, and it appears that this still happens the same way in SQL Server 2008: GetUtcDate() is evaluated twice in the execution plan. It is not going to produce different results per row, but it is possible that it could produce a different result per column if the timing ended up just right.

Edit

I can actually prove this behavior! Try this:

select GETUTCDATE(), RAND(), RAND(), ...[~3000 RAND()s]..., RAND(), GETUTCDATE()
from [TableOfYourChoice]

In my experiment, I ended up with 2011-05-17 20:47:34.247 in the first column and 2011-05-17 20:47:34.250 in the final column, showing a difference of three milliseconds as a result of the evaluation of all the RAND()s between the first and second calls to GETUTCDATE().

Community
  • 1
  • 1
StriplingWarrior
  • 151,543
  • 27
  • 246
  • 315
10
DECLARE @Counter INT = 1

WHILE (1 = (SELECT 1 WHERE GETUTCDATE()  = GETUTCDATE()))
SET @Counter = @Counter+1

SELECT @Counter /*Returns almost immediately with a number in the 000s for me.*/

And just to prove this happens when in the SELECT list also.

DECLARE @T TABLE 
(
rownum INT IDENTITY(1,1) PRIMARY KEY,
d1 datetime,
d2 datetime
)

WHILE (NOT EXISTS(SELECT * FROM @T WHERE d1 <> d2))
    BEGIN
    DELETE FROM @T
    INSERT INTO @T 
    SELECT GETUTCDATE(),GETUTCDATE()
    END

SELECT * FROM @T

BTW: IF for some reason you want to evaluate GETUTCDATE() on a per row basis you can wrap it in a scalar UDF.

CREATE FUNCTION dbo.GETUTCDATE()
RETURNS DATETIME
WITH SCHEMABINDING
AS
BEGIN
RETURN GETUTCDATE()
END
GO

SELECT GETUTCDATE(),dbo.GETUTCDATE()
FROM master..spt_values
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
3

It will be the same value.

GETDATE and GETUTCDATE are some of the functions that are evaluated once per query: not per row or column in that query. The optimiser will ensure they are the same because it updates the values at the same time

Another option is to define a DEFAULT constraints so you can do this and worry less about it.

UPDATE dbo.MyTable
SET CreationDate = DEFAULT, ModifiedDate = DEFAULT, ...
...

I have tables with similar columns with DEFAULT constraints and never had an issue. This also means I never have to think about what function I use in code.

Edit:

I could be wrong: SQL Server: intrigued by GETDATE()

Or I could be right: Selecting GETDATE() function twice in a select list-- same value for both?

Article: Conor Cunnigham mentions it the behaviour

Edit2: I'm demonstratably wrong: see StriplingWarrior's self answer. It's evaluated per column (not per row and not per query)

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Do you know if that is that officially documented anywhere? I would not want to rely on that behavior unless it was a documented feature, and I have been unsuccessful in locating such a thing in the past. – Jeffrey L Whitledge May 17 '11 at 19:54
  • @Jeffrey L Whitledge: It's documented indirectly in many places about the evaluation of functions per query, not per row. Including here on SO. Will check though. – gbn May 17 '11 at 19:56
  • +1, and thanks for the excellent links. You're right that it isn't evaluated *per row*, but from what I gathered, it appears to be evaluated *once per column*, but it happens so quickly that it'll probably come out the same 99.99% of the time. – StriplingWarrior May 17 '11 at 20:49
  • 2
    @StriplingWarrior - If it's the same 99.99% of the time, and your logic depends on the columns being the same, and if you have one million rows in your table, then you'll have 100 rows that are wrong! Probably better to stick with a variable. – Jeffrey L Whitledge May 17 '11 at 21:01
  • @Jeffrey L Whitledge: Agreed. That's why I gave a +1, but didn't mark this answer as correct. – StriplingWarrior May 17 '11 at 21:04
3

Keeping the GETUTCDATE() in a variable is a better option as it assures that CreationDate and ModifiedDate remains same. However, I called GETUTCDATE() number of time per query and they all returned the same value, so to me it seems that GETUTCDATE() value remains same per query.

FIre Panda
  • 6,537
  • 2
  • 25
  • 38
0

Based on extensive experiments in SQL Server 2008, I believe the following characterization is correct:

In a single SELECT, INSERT, or UPDATE query, each appearance of a date and time function will return the same value everywhere it appears across all rows and columns, including column default values.

Two different date and time functions (e.g., GETUTCDATE() and GETDATE()) may return different times from each other (even after adjusting for time zones or whatever).

Multiple queries in a single batch may return different values.

SELECT GETUTCDATE(), GETUTCDATE() -- These will be the same

SELECT GETUTCDATE()  --  These may
SELECT GETUTCDATE()  --  be different

This behavior is not documented anywhere, and the use of a variable makes the intention clear, so I probably wouldn't rely on this behavior unless avoiding this reliance is a major burden.

Jeffrey L Whitledge
  • 58,241
  • 9
  • 71
  • 99