rowversion (Transact-SQL)

Remarks

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. This property makes a rowversion column a poor candidate for keys, especially primary keys. Any update made to the row changes the rowversion value and, therefore, changes the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. If the column is in an index key, all updates to the data row also generate updates of the index.

timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms. In DDL statements, use rowversion instead of timestamp wherever possible. For more information, see Data Type Synonyms (Transact-SQL).

The Transact-SQL timestamp data type is different from the timestamp data type defined in the ISO standard.

In a CREATE TABLE or ALTER TABLE statement, you do not have to specify a column name for the timestamp data type, for example:

CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, timestamp);

If you do not specify a column name, the SQL Server Database Engine generates the timestamp column name; however, the rowversion synonym does not follow this behavior. When you use rowversion, you must specify a column name, for example:

CREATE TABLE ExampleTable2 (PriKey int PRIMARY KEY, VerCol rowversion) ;

A nonnullable rowversion column is semantically equivalent to a binary(8) column. A nullable rowversion column is semantically equivalent to a varbinary(8) column.

You can use the rowversion column of a row to easily determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the rowversion value is updated. If no change is made to the row, the rowversion value is the same as when it was previously read. To return the current rowversion value for a database, use @@DBTS.

You can add a rowversion column to a table to help maintain the integrity of the database when multiple users are updating rows at the same time. You may also want to know how many rows and which rows were updated without re-querying the table.

For example, assume that you create a table named MyTest. You populate some data in the table by running the following Transact-SQL statements.

CREATE TABLE MyTest (myKey int PRIMARY KEY
    ,myValue int, RV rowversion);
GO 
INSERT INTO MyTest (myKey, myValue) VALUES (1, 0);
GO 
INSERT INTO MyTest (myKey, myValue) VALUES (2, 0);
GO

You can then use the following sample Transact-SQL statements to implement optimistic concurrency control on the MyTest table during the update.

DECLARE @t TABLE (myKey int);
UPDATE MyTest
SET myValue = 2
    OUTPUT inserted.myKey INTO @t(myKey) 
WHERE myKey = 1 
    AND RV = myValue;
IF (SELECT COUNT(*) FROM @t) = 0
    BEGIN
        RAISERROR ('error changing row with myKey = %d'
            ,16 -- Severity.
            ,1 -- State 
            ,1) -- myKey that was changed 
    END;

myValue is the rowversion column value for the row that indicates the last time that you read the row. This value must be replaced by the actual rowversion value. An example of the actual rowversion value is 0x00000000000007D3.

You can also put the sample Transact-SQL statements into a transaction. By querying the @t variable in the scope of the transaction, you can retrieve the updated myKey column of the table without requerying the MyTest table.

The following is the same example using the timestamp syntax:

CREATE TABLE MyTest2 (myKey int PRIMARY KEY
    ,myValue int, TS timestamp);
GO 
INSERT INTO MyTest2 (myKey, myValue) VALUES (1, 0);
GO 
INSERT INTO MyTest2 (myKey, myValue) VALUES (2, 0);
GO
DECLARE @t TABLE (myKey int);
UPDATE MyTest2
SET myValue = 2
    OUTPUT inserted.myKey INTO @t(myKey) 
WHERE myKey = 1 
    AND TS = myValue;
IF (SELECT COUNT(*) FROM @t) = 0
    BEGIN
        RAISERROR ('error changing row with myKey = %d'
            ,16 -- Severity.
            ,1 -- State 
            ,1) -- myKey that was changed 
    END;

See Also

Reference