sql-serverauto-incrementtemporal-tables

Identity out of order with system versioned table


I'm using system-versioned (temporal) tables with an identity column and noticed that sometimes the start time of a row in the history table has a higher identity value than a row with a later start time. See example below:

StartTime EndTime Identity
2022-09-21 14:10:25.4181267 2022-09-21 16:32:14.5724582 2
2022-09-21 14:10:26.1524526 2022-09-21 15:46:19.6136239 1

Is this because of overlapping transactions - i.e one row "claimed" the earlier identity value but was then inserted slightly after another set of values?


Solution

  • From the document you linked

    INSERTS: The system sets the value for the ValidFrom column to the begin time of the current transaction...

    But IDENTITY columns take their values at the moment of insertion. So yes, there may be an overlap.