How are SQLServer Timestamp/RowVersion Values Created?

Question

I am relatively fresh to SQLServer's use of ROWVERSION/TIMESTAMP data, and normally just query for required rows based on modified dates.

How is the ROWVERSION assigned to a new row in a database? Does every new row start with the same rowversion, or is it a unique value? In other words, will a new row in the database have its rowversion set to currentMaxRowVersion+1?

The use case for this is to query for all rows whose data has changed since the last time the database was queried, EG:

SELECT * FROM MyTable WHERE RowVersion > previousHighestRowVersion

• Dan Guzman: For your use case, consider Change Data Capture. To use ROWVERSION, you'd need to save the max value every time the table is queried.
• Donglecow: Thanks for the tip. Unfortunately we don't have access to make changes to the DB. We'll be able to keep track of the ROWVERSION when we query the database though, as our software has a mechanism for that sort of thing.

Answer 1

Referencing the documentation for rowversion (Transact-SQL) - (highlighting mine)

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.

---

You can use the rowversion column of a row to easily determine whether the row has had an update statement ran against it since the last time it was read. If an update statement is ran against the row, the rowversion value is updated. If no update statements are ran against the row, the rowversion value is the same as when it was previously read.

---

As far as being unique - 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.

---

You can check the current database rowversion by using @@DBTS. This example shows rowversion in action.

set nocount on
drop table if exists mytest
go
CREATE TABLE MyTest (myKey int PRIMARY KEY 
 ,myValue int, RV rowversion); 
GO 
INSERT INTO MyTest (myKey, myValue) VALUES (1, 0); 
GO 
select @@DBTS
go
INSERT INTO MyTest (myKey, myValue) VALUES (2, 0); 
GO 
select @@DBTS
go
update MyTest set myValue = myValue
go
select @@DBTS
go

---

------------------
0x0000000000009481
------------------
0x0000000000009482
------------------
0x0000000000009484

• Donglecow: Thank you. So while new rows won't be currentMaxRowVersion+1, they will be able to be found by querying WHERE rowversion > currentMaxRowVersion? If duplication does occur, I assume another update on that row will cause the rowversion to increment and become unique again?

Answer 2

There is a single counter for the database. All tables' values will be drawn from this single source. This is mentioned in the documentation:

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 can be shown with a simple example. I'll create a new database and two tables

create database DBX;
use dbx;
create table t1(cola int, colb rowversion);
create table t2(cola int, colb rowversion);

Adding a row will produce a rowversion value

insert t1(cola) values (1);
select * from t1

On my system this returns

cola colb
----------- ------------------
1 0x00000000000007D1

Your system may produce a different number. The value is hexadecimal, and meaningless.

Adding a row to t2

insert t2(cola) values (2);

produces this:

cola colb
----------- ------------------
1 0x00000000000007D1
cola colb
----------- ------------------
2 0x00000000000007D2

Repeating produces the expected result, rowversion increments

insert t1(cola) values (3);
cola colb
----------- ------------------
1 0x00000000000007D1
3 0x00000000000007D3
cola colb
----------- ------------------
2 0x00000000000007D2

Note the new rowversion is one more than that in t1.

If I restart the instance and add a row something interesting happens

<restart>
insert t2(cola) values (4);
cola colb
----------- ------------------
1 0x00000000000007D1
3 0x00000000000007D3
cola colb
----------- ------------------
2 0x00000000000007D2
4 0x0000000000002711

The rowversion has jumped significantly. So it is monotonic, but not continuous over an instance restart. Similar behaviour has been observered with SEQUENCES and IDENTITY columns.

So, rowversion can be used to label a "high water" mark in an ETL process. It cannot be used exclusively for a particular table, however. An IDENTITY would be a better choice for that purpose. I have no idea what SQL Server would do if rowversion were ever to overflow.

• Tibor Karaszi: It used to be that you got a warning (935) when there were 1,000,000 values left for the timestamp, and after that you can't do any more modifications in the database. That message number is not for something different,makes me wonder if the message was removed when we went from 6 byts to 8 bytes (and "the value will never overflow" mentality)...