Schema - Do you have a rowversion column?

Last updated by Brady Stroud [SSW] about 1 month ago.See history

SQL Server rowversions are a data type available which are binary numbers that indicate the relative sequence in which data modifications took place in a database. See the MSDN article on rowversions here: rowversion (Transact-SQL)

All tables should have a rowversion column called "RecordVersion" to aid concurrency checking. A rowversion improves update performance because only one column needs to be checked when performing a concurrency check (instead of checking all columns in a table for changes). NoRowversionOnTable

Figure: Bad Example - No rowversion available in this table

CREATE TABLE MyTest (myKey int PRIMARY KEY
    ,myValue int, RecordVersion rowversion);
GO

INSERT INTO MyTest (myKey, myValue) VALUES (1, 0);
INSERT INTO MyTest (myKey, myValue) VALUES (2, 0);
INSERT INTO MyTest (myKey, myValue) VALUES (3, 0);
UPDATE MyTest SET myValue = 1 WHERE myKey = 2

SELECT * FROM MyTest ORDER BY RecordVersion DESC

Figure: Good Example - A create statement which builds a table with a rowversion

RecordsWithRowversion

Figure: Good Example - A set of records with a rowversion available

Adam Cogan
Alex Breskin
Christian Morford-Waite
We open source. Powered by GitHub