patternsqlMinor
Is rowversion unique across the database? Or per table?
Viewed 0 times
uniquetheperrowversiondatabaseacrosstable
Problem
If I have a rowversion on two different tables in a database, are they guaranteed to be unique across both tables? If not, is there any way to set this behaviour?
I have a view that joins two tables together, I'm wondering if this behaviour is guaranteed, so that I could take the largest row number from either table, to form a rowversion for the view.
I have a view that joins two tables together, I'm wondering if this behaviour is guaranteed, so that I could take the largest row number from either table, to form a rowversion for the view.
Solution
According to this link, it 'should' be unique across all tables in a database.
Here is the 'should' part - there is a caveat in the documentation:
Duplicate rowversion values can be generated by using the SELECT INTO
statement in which a rowversion column is in the SELECT list. We do
not recommend using rowversion in this manner.
And here is a summary of the information in the link:
Each database has a counter that is incremented for each insert or
update operation that is performed on a table that contains a
rowversion column within the database. This counter is the database
rowversion. This tracks a relative time within a database, not an
actual time that can be associated with a clock. A table can have only
one rowversion column. Every time that a row with a rowversion column
is modified or inserted, the incremented database rowversion value is
inserted in the rowversion column.
Here is the 'should' part - there is a caveat in the documentation:
Duplicate rowversion values can be generated by using the SELECT INTO
statement in which a rowversion column is in the SELECT list. We do
not recommend using rowversion in this manner.
And here is a summary of the information in the link:
Each database has a counter that is incremented for each insert or
update operation that is performed on a table that contains a
rowversion column within the database. This counter is the database
rowversion. This tracks a relative time within a database, not an
actual time that can be associated with a clock. A table can have only
one rowversion column. Every time that a row with a rowversion column
is modified or inserted, the incremented database rowversion value is
inserted in the rowversion column.
Context
StackExchange Database Administrators Q#150473, answer score: 8
Revisions (0)
No revisions yet.