SQL Server ROWVERSION (TIMESTAMP) Data Type

SQL Server rowversion is an automatically generated 8-byte binary value for tracking changes to rows. It does not store a date or time. SQL Server’s timestamp type name is a deprecated synonym for rowversion, and it is unrelated to the ISO SQL timestamp type. Use the explicit name rowversion for new table definitions. See Microsoft’s rowversion reference.

Syntax

rowversion

A table can have only one rowversion column. SQL Server maintains a database-level counter and assigns the next value when a row in a table with a rowversion column is inserted or updated. The value can help determine whether that row has changed since it was read, but it does not encode when the change happened and is not a good primary key.

An UPDATE advances the value even when it assigns a column its existing value. Avoid updating rows solely to “refresh” the token: doing so changes the token and may cause other clients to detect a conflict.

Do not use SELECT INTO with a rowversion column in the select list as a way to generate new concurrency tokens. Microsoft notes that this operation can produce duplicate values; let inserts and updates on the destination table’s own rowversion column generate its tokens.

Use rowversion for optimistic concurrency

Define the column with an explicit name:

CREATE TABLE dbo.Products (
    ProductID int PRIMARY KEY,
    ProductName nvarchar(200) NOT NULL,
    RowVersion rowversion
);

When reading a row, keep its RowVersion value. Include that original value in the UPDATE predicate so the statement changes the row only if nobody has updated it since the read:

UPDATE dbo.Products
SET ProductName = @new_name
OUTPUT inserted.ProductID, inserted.RowVersion
WHERE ProductID = @product_id
  AND RowVersion = @original_rowversion;

If the statement returns no row, the product was deleted or its RowVersion changed after it was read. The application can then reload the current row and ask the user to resolve the conflict. The returned RowVersion is the token to keep for the next update.

rowversion is not a timestamp

Use datetime2 or datetimeoffset when a column must record a date or time. rowversion values are binary counters scoped to one database; they are not portable clock values and do not tell you the elapsed time between changes. For the current database counter, SQL Server provides @@DBTS, but that value is not a substitute for reading the rowversion from a particular row.

For new schemas, use rowversion instead of the deprecated timestamp spelling. SQL Server may generate a column name automatically when the old timestamp spelling omits one; rowversion requires an explicit column name. See Microsoft’s data type synonyms and the date and time type reference.