Rowversion: Version Stamping A Row

Introduction

Very often while working with data, our requirement is to retrieve which data has been modified and keep the data in two tables synchronized. For achieving this task, SQL Server has provided many ways such as CDC (Change Data Capture), rowversion datatype, MERGE statement and various other alternatives using such as joins, storing datetime, etc., which can be used based on our requirement.

Here, we will discuss about rowversion datatype and see a small demo of how we use rowversion Sync data changes done in a table to other table.

About rowversion

rowversion is a datatype which exposes 8 byte binary number, unique within a database. It is generally used for version stamping a table row. It means value of column of datatype rowversion if a particular row gets changed, whenever value of any column of that row gets changed(updated). When a new insertion is made in table rowversion datatype column automatically gets populated with a unique value.

How to Create a Table with rowversion Data Type?

CREATE TABLE Product
(
ProductID INT IDENTITY PRIMARY KEY
,ProductName VARCHAR(100) NOT NULL
,ProductCatagory VARCHAR(20) NOT NULL
,ManufacturedBy VARCHAR(100) NOT NULL
,ProductKey rowversion
--,ProductKey1 rowversion
) 

Here, the point to note is that only one column of rowversion datatype is allowed per table. So, if we try to create another column of the same datatype ProductKey1 by un-commenting it, we will end up with an error ("A table can only have one timestamp column. Because table 'Product' already has one, the column 'ProductKey1' cannot be added.")

Insertion and Updation

Insertion and Updation on rowversion datatype column is not allowed. If you try, you will get the below error:

INSERT INTO Product(ProductName , ProductCatagory, ManufacturedBy, ProductKey) 
VALUES ('Keyboard','Peripheral','Microsoft',CONVERT(BINARY(8),GETDATE()))
 
--Msg 273, Level 16, State 1, Line 1 Cannot insert an explicit value into a timestamp column

UPDATE Product 
SET ProductKey= '0x000000000E1EB636' 
WHERE ProductID = 1 
 
--Msg 272, Level 16, State 1, Line, Cannot update a timestamp column.

If you have noticed, every time SQL Server is generating an error, it is referring rowversion datatype as timestamp. This is because rowversion is the synonym for timestamp. Since in ANSI SQL, timestamp is a data type for date and time and SQL server doesn't track an actual time that can be associated with a clock date and time, but represents relative time within a database. Microsoft decided to deprecate timestamp syntax in future versions and provided a synonym rowversion.

How Does It Work?

MSDN states that every database has a counter referred as database rowversion. This counter gets incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database and this value is inserted or updated in rowversion column of the table. If at any point of time, you need to check value of this counter, use this:

SELECT @@DBTS; 

Demo

Apart from the uses explained on MSDN, we can also use this to track changes in one table and update the changes in another table. Let's take a look at a small demonstration.

Before we start, please treat the below code written for illustration purposes only. Now let's say we have two tables, one Product (created above) and another PurchaseOrder. Somehow, our requirement is to keep update data of columns [ProductId], [ProductName] and [ProductCategory] of Product Table into PurchaseOrder table along with some other data. We can do this by creating a column [ProductKey] of rowversion datatype in Product table, so that we can find out data of which row has been changed from last time we read data from it and a column of BINARY datatype in PurchaseOrder table to store value of [ProductKey] column of Product table.

Let's create PurchaseOrder table, insert some test data in Product table and also in PurchaseOrder table.

CREATE TABLE PurchaseOrder
(
  ProductId INT FOREIGN KEY REFERENCES Product(ProductId)
, ProductName VARCHAR(100) NOT NULL
, ProductCatagory VARCHAR(20) NOT NULL
, Price DECIMAL NOT NULL
, Quantity INT
, ProductKey BINARY(8)
)
 
-- Insert test Data in Product Table
INSERT INTO Product(ProductName , ProductCatagory, ManufacturedBy) 
VALUES ('Keyboard','Peripheral','Microsoft')
	, ('Mouse','Peripheral','Microsoft')
	, ('Headphone','Peripheral','Microsoft')
 
-- Insert test data in PurchaseOrder Table
INSERT INTO PurchaseOrder (ProductId , ProductName , ProductCatagory , Price , Quantity , ProductKey)
SELECT ProductId , ProductName , ProductCatagory , ProductId * 100 , ProductId*2 , ProductKey
FROM Product 

By this time, when you will execute SELECT Statement on both tables, you will find that both tables are in Sync. Now let's change the data in Product table.

UPDATE Product 
SET ProductName = 'WireLess Keyboard' , ProductCatagory ='Keyboard'
OUTPUT deleted.ProductKey 'Old Prod Key'
WHERE ProductName = 'Keyboard'

You can use the below query to check, which data has been changed.

SELECT P.* FROM Product P
JOIN PurchaseOrder PO
	ON P.ProductID = PO.ProductId
	AND P.ProductKey <> PO.ProductKey 

For synchronization, you can use either of the following ways:

-- 1: Using JOIN

UPDATE PO
	SET ProductName= P.ProductName 
		, ProductCatagory = P.ProductCatagory 
		, ProductKey = P.ProductKey 
FROM Product P
JOIN PurchaseOrder PO
	ON P.ProductID = PO.ProductId
	AND P.ProductKey <> PO.ProductKey 
 
-- 2: Using MERGE Statement

BEGIN TRAN
;
MERGE INTO PurchaseOrder TargetTable
USING Product SourceTable
	ON TargetTable.ProductId = SourceTable.ProductId
WHEN MATCHED 
	AND TargetTable.ProductKey <> SourceTable.ProductKey 
THEN
	UPDATE 
	SET	  ProductName= SourceTable.ProductName 
		, ProductCatagory = SourceTable.ProductCatagory 
		, ProductKey = SourceTable.ProductKey 
;	

Points to Remember

• Since Data of rowversion column gets changed, if any update occurs on that row, it is a very poor candidate for Keys. If it is a key then, even update on any non key column, will also generate Index Update.
• We should not make it Primary Key as well because of the Index Update explained above and also referencing foreign key can lose their values which they are referring to.
• Value of rowversion gets changed even for false update. See result of below code, we are updating ProductName with the same value 'Keyboard', which is its current value, even then the value of ProductKey [rowversion DataType] column got changed.

  

References

• More about rowversion datatype
• More about OUTPUT clause
• More about MERGE statement

!! Happy programming !!