SQL Server Temporal Tables and Rowversions
I was intrigued to see how a table with System_Versioning enabled would behave if it had a rowversion on the primary table, and how it would get inserted to the history table.
The short answer is that it copies the rowversion of the previous change to the record into the history table. This feels slightly counterintuitive from a rowversion perspective as every change to a row will generate a new database level rowversion and set it on that record. But it is in-keeping with how temporal tables work in SQLServer (i.e. theres no state except the start/end dates so a record is copied exactly into the history table).
It should also be noted that there doesn’t appear to be a way to add a rowversion to the history table either. I.e. it must match the primary tables columns, so no you can’t just set the column to varbinary and add a new rowversion column that behaves more like a rowversion.
Heres some Proof
The first 3 selects produce:
As we can see… the row version placed into the history table on the second select is the rowversion for when the first was inserted, and is not updated when it is placed into the history table.
And the third select (i.e. the delete):
The newly added history record contains the rowversion from the previous update statement.