HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Is rowversion unique across the database? Or per table?

Submitted by: @import:stackexchange-dba··
0
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.

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.

Context

StackExchange Database Administrators Q#150473, answer score: 8

Revisions (0)

No revisions yet.