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
)
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()))
UPDATE Product
SET ProductKey= '0x000000000E1EB636'
WHERE ProductID = 1
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 INTO Product(ProductName , ProductCatagory, ManufacturedBy)
VALUES ('Keyboard','Peripheral','Microsoft')
, ('Mouse','Peripheral','Microsoft')
, ('Headphone','Peripheral','Microsoft')
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:
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
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
!! Happy programming !!