IDENTITY and GetDate not working

Question

I have a table with an IDENTITY column and a DateTime column set by GetDate () as follows:

CREATE TABLE [MyTable]( [Id] [int] IDENTITY(1,1) , [InsertTime] [datetime] DEFAULT (getdate()), [OtherValues] [int] ) 

All INSERTs have default values ​​for IDENTITY and DateTime columns as follows:

 INSERT INTO [MyTable] ([OtherValues]) VALUES (1) 

always as standalone statements outside of any explicit transaction.

I would expect that Id will strictly increase, and InsertTime will also increase, but not strictly. But with a lot of work, we see several such examples:

 | Id | InsertTime | |------|-------------------------| | 3740 | 2015-03-05 10:07:25.560 | | 3741 | 2015-03-05 10:07:25.557 | | 3742 | 2015-03-05 10:07:25.577 | 

where we have a slight drop in InsertTime.

Does anyone know how this happens and what is the “correct” line order?

Answer 1

There may be problems with both the columns of columns and columns of DateTime, which can lead to unexpected results, however, I recommend using the identifier value rather than the DateTime value to determine the order of the inserts.

While DateTime theoretically always increases over time, there may be a lack of accuracy due to how the current time is retrieved from the system. For all the details here is a link that explains a bit what is happening .

An identifier column is usually expected to be at least upstream, even if it is not sequential. However, it should be borne in mind that the identifier column can also lead to unexpected results if the seed is changed, the increment changes, the server reboots, etc. Microsoft has provided a list of what is and is not guaranteed for the identity values on this page .

Another thing to keep in mind is that if you use transactions, the identifier can be generated and accepted by this transaction, and then the subsequent transaction can take the next identification value and theoretically can complete its changes before the first transaction.

Answer 2

The correct order of entries is the one described by the Identity column.
The deviation that you describe is within the acceptable accuracy range for the DateTime data type (this is 0.00333 ms).

The problem is that the DateTime data type is simply not so accurate. you should use Datetime2 if your version of sql server supports it (i.e. 2008 or higher).

datetime values ​​are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

Check out this comparison table between date and time data types on MSDN.

Read this article for a detailed explanation.

Answer 3

The "right" row order is the order of the identity column. Although the primary purpose of the identity column is to provide a unique key, it is guaranteed to be unique and upstream. The datetime column does not specify the accuracy of determining the correct sort order. Even if you switched to the datetime2 column, there is no requirement that the time should always be upstream or unique.

They are also “correct” from a certain point of view. The identifier column correctly represents the order in which the identifier value is determined, and the datetime column correctly represents the time during which the current time has been read.

Since the database server performs multitasking / multithreading, it cannot guarantee that the entire transaction will take place before another transaction. Due to the fact that the tasks are serviced inside the database, you can start one transaction, another - start and end, and then complete the first.