4

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?

SKK
  • 39
  • 1
  • 5
  • 2
    I guess you can create an after insert trigger on both tables which use the time in one column to update the datetime column in other table. – M.Ali Nov 07 '13 at 20:00
  • You're inserting more than a million rows at a time? Have you considered overriding the default with a variable that is defined and populated once, instead of relying on the default? – Aaron Bertrand Nov 07 '13 at 20:51
  • Do you have an execution plan for the job; Do you know that SQL is physically performing the insert into TableA before the insert into TableB?: This may seem to be the logical order given your code, but is SQL compiling that code in the same way? – John Bingham Nov 07 '13 at 21:56
  • Are the inserts in single transactions or multiple transactions? – Gordon Linoff Nov 07 '13 at 22:02
  • Might table A or table B have the date column of the datatype datetime and not datetime2, that is are you sure they are both datetime2 – Hedinn Nov 08 '13 at 13:37
  • I have changed GETDATE to SYSDATETIME and will check results after heavy load on weekend. Until this moment there is no more errors and timestamps A and B are equal in most records, never A>B. – SKK Nov 08 '13 at 18:28

3 Answers3

1

If you insert and commit the first insert in table A and then insert and commit the second insert in table B, is very possible to get this result because is going to take that difference in time to insert the two records. Even if you don't commit the insert in separate transactions.

when the table grows the insert will take more and more time for many reasons. If the table has index in every insert the index has to take note where the record was saved or organized. Second the fragmentation in the pages internally in sql server will make the insert to take more time. Check your index structure if you want faster inserts keep the fill factor low.

If you want the same time always, create a variable get the time and then make the insert in both table using this variable.

Hope it helps.

Juan
  • 1,352
  • 13
  • 20
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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
0

GETDATE to SYSDATETIME change in constraints seems to solve problem.

SKK
  • 39
  • 1
  • 5