What is “rowversion” in SQL Server?

In SQL Server, rowversion is a data type that exposes automatically generated, unique binary numbers within a database. It allows you to version-stamp table rows with a unique value. This helps maintain the integrity of the database when multiple users are updating rows at the same time.

Each SQL Server database has a a counter that is incremented for each insert or update operation that is performed on a table that contains a column with the rowversion data type (or its timestamp synonym, which is flagged for deprecation).

If a table contains a rowversion (or timestamp) column, any time a row is inserted or updated, the value of the rowversion column is set to the current rowversion value. This is true, even when an UPDATE statement doesn’t result in any changes to the data.

Example 1 – Create a Table with a rowversion Column

Here’s an example of creating a table with a rowversion column.

CREATE DATABASE Test_rowversion;
USE Test_rowversion;
CREATE TABLE Pet (
    PetId int IDENTITY(1,1) PRIMARY KEY, 
    PetName varchar(255),
    VersionStamp rowversion
    );

SELECT @@DBTS AS Before;

INSERT INTO Pet (PetName)
VALUES ('Max');

SELECT @@DBTS AS After;

Result:

Commands completed successfully.
Changed database context to 'Test_rowversion'.
Commands completed successfully.
+--------------------+
| Before             |
|--------------------|
| 0x00000000000007D0 |
+--------------------+
(1 row affected)
(1 row affected)
+--------------------+
| After              |
|--------------------|
| 0x00000000000007D1 |
+--------------------+
(1 row affected)

Take a look at the “Before” and “After” values in the results. This represents the current rowversion value.

In this case I created a new database and table, and the rowversion value started at 0x00000000000007D0. Once I inserted a row, the rowversion was incremented to 0x00000000000007D1.

Example 2 – Updates

As mentioned, the rowversion value is also incremented when you do an update.

Example:

SELECT * FROM Pet;

UPDATE Pet
SET PetName = 'Maxine'
WHERE PetId = 1;

SELECT * FROM Pet;

Result:

+---------+-----------+--------------------+
| PetId   | PetName   | VersionStamp       |
|---------+-----------+--------------------|
| 1       | Max       | 0x00000000000007D1 |
+---------+-----------+--------------------+
(1 row affected)
(1 row affected)
+---------+-----------+--------------------+
| PetId   | PetName   | VersionStamp       |
|---------+-----------+--------------------|
| 1       | Maxine    | 0x00000000000007D2 |
+---------+-----------+--------------------+
(1 row affected)

Here, I update the PetName column, and the VersionStamp column (rowversion) is incremented to 0x00000000000007D2.

Example 3 – Updates with No Changes

One of the cool things about rowversion is that it is incremented on all UPDATE operations even when no change takes place.

For example, if I run the previous code again, the value of the VersionStamp column still changes, even though no change took place:

SELECT * FROM Pet;

UPDATE Pet
SET PetName = 'Maxine'
WHERE PetId = 1;

SELECT * FROM Pet;

Result:

+---------+-----------+--------------------+
| PetId   | PetName   | VersionStamp       |
|---------+-----------+--------------------|
| 1       | Maxine    | 0x00000000000007D2 |
+---------+-----------+--------------------+
(1 row affected)
(1 row affected)
+---------+-----------+--------------------+
| PetId   | PetName   | VersionStamp       |
|---------+-----------+--------------------|
| 1       | Maxine    | 0x00000000000007D3 |
+---------+-----------+--------------------+
(1 row affected)

The value of the PetId and PetName columns didn’t change, but the VersionStamp column was incremented.

Example 4 – The timestamp Data Type

The timestamp data type is a synonym for rowversion. However, timestamp is one of the data types that have been flagged for deprecation in a future version of SQL Server. Unsurprisingly, Microsoft recommends to avoid using this feature in new development work, and to plan to modify applications that currently use this feature.

Therefore, if you encounter a database that uses the timestamp data type, you might want to think about changing it to rowversion.

Just for demonstration purposes, here’s a modification of the first example to use timestamp instead of rowversion:

CREATE DATABASE Test_timestamp;
USE Test_timestamp;
CREATE TABLE Pet (
    PetId int IDENTITY(1,1) PRIMARY KEY, 
    PetName varchar(255),
    timestamp
    );

SELECT @@DBTS AS Before;

INSERT INTO Pet (PetName)
VALUES ('Max');

SELECT @@DBTS AS After;

Result:

Commands completed successfully.
Changed database context to 'Test_timestamp'.
Commands completed successfully.
+--------------------+
| Before             |
|--------------------|
| 0x00000000000007D0 |
+--------------------+
(1 row affected)
(1 row affected)
+--------------------+
| After              |
|--------------------|
| 0x00000000000007D1 |
+--------------------+
(1 row affected)

And while we’re at it, here’s the next example modified for timestamp:

SELECT * FROM Pet;

UPDATE Pet
SET PetName = 'Maxine'
WHERE PetId = 1;

SELECT * FROM Pet;

Result:

+---------+-----------+--------------------+
| PetId   | PetName   | timestamp          |
|---------+-----------+--------------------|
| 1       | Max       | 0x00000000000007D1 |
+---------+-----------+--------------------+
(1 row affected)
(1 row affected)
+---------+-----------+--------------------+
| PetId   | PetName   | timestamp          |
|---------+-----------+--------------------|
| 1       | Maxine    | 0x00000000000007D2 |
+---------+-----------+--------------------+
(1 row affected)

You might have noticed that I didn’t actually provide a name for the timestamp column. If you don’t specify a column name, the SQL Server Database Engine generates the timestamp column name.

However, rowversion doesn’t allow this syntax, so you’ll need to specify the column name when using rowversion.