SQL Server 2012: DATETIME discrepancies between inserts and triggers

Question

We are having a very strange problem whereby when calling GETUTCDATE() the returned value is very slightly earlier in the second statement than the first. The scenario we have is the following:

1. We insert into a table to track a users current status, this table has a trigger on which inserts into an associated history table of the users past statuses with a DATETIME field and the insert calls GETUTCDATE()

2. Once this is done we have another table with an associated record we insert into calling GETUTCDATE() which tracks which part updated the user status.

So the flow would be:

1. Insert into UserStatuses
2. Trigger fires on UserStatuses table inserting into UserStatusesHistory calling GETUTCDATE()
3. Then we insert into OwningStatuses table calling GETUTCDATE()

We are finding that on some occasions the DATETIME on the UserStatusesHistory is after the DATETIME on the OwningStatuses table.

How can this be when the GETUTCDATE() on the OwningStatuses is called after the GETUTCDATE() for the UserStatusesHistory table?

In some scenarios are triggers run asynchronously? (I can't believe this as it's against everything I have read and we are not using any service brokers).

Is it possible the GETUTCDATE() is cached at the beginning of the procedure on some occasions and this cached value isn't carried into the trigger?

Answer 1

Due to the declarative nature of SQL, the database engine might take the liberty to evaluate parts of a SQL statement in any order it sees fit (as long as it doesn't affect semantics). Your suggestion that GETUTCDATE() might be cached, is a plausible one.

I know this does not answer your question. But whatever the implementation of GETUTCDATE in SQL2012, it might well change in a future version. So avoid relying on it, or future upgrades could become a real pain. Implement your logic in a way that does not rely on any assumptions regarding evaluation order.

In your specific case, I see a few possible solutions.

1. If you have no problem with OwningStatuses having a time slightly later than UserStatusesHistory, then it might already help if you send the third step off to SQL Server as a separate batch.
2. Swap steps 2 and 3; and let the trigger query OwningStatuses instead of making up its own date.
3. Stop using triggers; there is more than one reason to consider this.

Answer 2

The hardware / OS clock was adjusted between the calls. Happenss.

You want cached values - you have to reprogram all access to set a variable first, then use that value in subsequent calls ;(