Introduction
Very often, when working with data, our requirement is to retrieve the data that has been modified and maintain the data in two synchronized tables. To do this, SQL Server has provided many such as Change Data Capture (CDC), rowversion datatype, the MERGE statement and various other alternatives using such as joins, storing datetime and so on, that can be used based on our requirements.
Here, we will explain the rowversion datatype and see a small demo of how to use rowversion to Sync data changes done in a table to another table.
About rowversion
rowversion is a datatype that exposes an 8-byte binary number, unique within a database. It is generally used for version stamping a table row. It means that, for the value of a column of a datatype, the rowversion f is a specific row that is changed whenever the value of a column of that row is changed (updated). When a new insertion is made in a table rowversion datatype, the column is automatically 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 <span style="color: rgb(153, 0, 0); font-size: 15px; white-space: normal;">rowversion</span>
--,ProductKey1 <span style="color: rgb(153, 0, 0); font-size: 15px; white-space: normal;">rowversion</span>
)
Here, the point to note is that, only one column of the 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.").
Inserting and Updatiing
Inserting and updating a rowversion datatype column is not allowed. If you try, you will get the following 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, everytime SQL Server generates an error, it is referring to the rowversion datatype as timestamp. This is because rowversion is the synonym for timestamp. 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 the synonym rowversion.
How it works
MSDN states that every database has a counter, referred to as the database rowversion This counter is 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 the rowversion column of the table. If at any point of time you need to check the value of this counter, use this:
SELECT @@DBTS;
Demo
Apart from the uses explained in MSDN, we can also use this to track changes in one table and update the changes in another table. Let's see a small demonstration.
Before we start, please treat the following code as 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 the updates data of the columns [ProductId], [ProductName] and [ProductCategory] of Product Table in the 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 determine which row has been changed from the last time we read the data from it and a column of BINARY datatype in the PurchaseOrder table to store the value of the [ProductKey] column of the Product table.
Let's create a PurchaseOrder table, insert some test data in the Product table and also in the 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 now, when you execute the SELECT statement on both tables, you will find both tables are synchronized. Now let's change some data in the Product table.
UPDATE Product
SET ProductName = 'WireLess Keyboad' , ProductCatagory ='Keyboad'
OUTPUT deleted.ProductKey 'Old Prod Key'
WHERE ProductName = 'Keyboard'
You can use the following query to determine what data has been chnaged.
SELECT P.* FROM Product P
JOIN PurchaseOrder POON P.ProductID = PO.ProductIdAND P.ProductKey <> PO.ProductKey
For Syncronization you can use either of follwing way.
-- 1: Using JOIN
UPDATE POSET ProductName= P.ProductName , ProductCatagory = P.ProductCatagory , ProductKey = P.ProductKey
FROM Product P
JOIN PurchaseOrder POON P.ProductID = PO.ProductIdAND P.ProductKey <> PO.ProductKey -- 2: Using MERGE Statement
BEGIN TRAN
;
MERGE INTO PurchaseOrder TargetTable
USING Product SourceTableON TargetTable.ProductId = SourceTable.ProductId
WHEN MATCHED AND TargetTable.ProductKey <> SourceTable.ProductKey
THENUPDATE SET ProductName= SourceTable.ProductName , ProductCatagory = SourceTable.ProductCatagory , ProductKey = SourceTable.ProductKey
;
Points to remember
Since the data of a rowversion column is changed, if any update occurs on that row then it is a very poor candidate for keys. If it is a key, then even if we update a non-key column, it will generate an Index Update.
We should not make it Primary Key as well because of the Index Update explained above and also referencing a foreign key can loose the values that are being referring to.
The value of a rowversion is changed even for a false update. See the result of the following code. We are updating the ProductName with the same value 'Keyboard', that is its current value, even then the value of the ProductKey [rowversion DataType] column got changed.