0

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?

SBFrancies
  • 3,987
  • 2
  • 14
  • 37
  • 1
    "The system sets the value for the ValidFrom column to the begin time of the current transaction" which means that if there is a delay in between tran1 starting and inserting, in which tran2 starts and inserts, then yes there will be an overlap. Why does it matter? – Charlieface Sep 28 '22 at 14:04
  • It doesn't matter in itself, it's more something I didn't necessarily expect so I thought it would be worthwhile to clarify what the actual expected behaviour is. Thank you for the clarification, I was struggling to find it in the docs. – SBFrancies Sep 28 '22 at 14:20

1 Answers1

1

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.

Charlieface
  • 52,284
  • 6
  • 19
  • 43