Home
Schema - Do you have a rowversion column?
  v7.0 Posted at 26/03/2020 3:12 PM by Christian Morford-Waite

​​​​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).​​

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


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


Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address:

    Comments: