GETDATE in DEFAULT constraint on some tables in SQL Server 2012

Question

As u expect I have got problem with GETDATE in DEFAULT constraint on some tables in SQL Server 2012.

I have got two tables like below (A and B):

CREATE TABLE [dbo].[TABLE_A_OR_B] (
[TABLE_A_OR_B_PK]   BIGINT          IDENTITY (1, 1) NOT NULL,
[CREATE_DATETIME]   DATETIME2 (7)   CONSTRAINT [DF_TABLE_A_OR_B_CREATE_DATETIME] DEFAULT (getdate()) NOT NULL,
[CREATE_USER]       VARCHAR (100)   CONSTRAINT [DF_TABLE_A_OR_B_CREATE_USER] DEFAULT (suser_sname()) NOT NULL,
...
CONSTRAINT [PK_TABLE_A_OR_B] PRIMARY KEY CLUSTERED ([TABLE_A_OR_B_PK] ASC)
);

And i have got procedure where I’m doing two inserts - first to table A and second to B without column CREATE_DATETIME. Between them is a lot of stuff.

Now guess what is in column CREATE_DATETIME in tables A and B?

Two times - maybe after 1 000 000 records, never before - there is in table A datetime greater than in table B for records from the same sp execution (verified) like:

row in A: 2013-11-07 00:02:22.7000000 
row in B: 2013-11-07 00:02:22.6970000

Can u give me some clues why?

Answers for comments:
1. No triggers.
2. No 1 000 000 records at a time, it is total count of records in table in moment of error first appearance. This information is for statistical analysis - today error occurred after xx thousands of records after last error - so it’s very random.
3. Yes, statements are 100% executed in this order.
4. No transaction or single - two different processes - same error.
5. Sure DATETIME2.

Important! Someone told me that GETDATE has accuracy to 3 milliseconds so maybe GETDATE rounds milliseconds with round robin method so two times for same or nearly same time (diff < 3ms) it can give two different approximations?

Answer 1

GETDATE() is derived from the operating system's clock - if something caused the clock on the server to change (to an earlier time), you'll achieve your (apparent) time travel.

What could cause such changes? The obvious are a manual adjustment, or if the server is set to automatically synchronize its clock with an external source - such as another machine on your domain or via NTP. There may be other possible causes also.