How to use rowversion to version-stamp table rows in SQL Server

 

      In my last post, I wrote about how to use CHECKSUM function to synchronize tables. SQL Server has another option for you if you need to synchronize tables. rowversion is a datatype that exposes automatically generated, unique binary numbers within a database.

     Each database has rowversion counter that gets updated by each insert or update operations. To enable it, all you need to do is to create a rowversion column in a table you like to track updates and inserts. Following command gives you the current rowstore value of the database. This value changes with each update or insert. rowversion will still be updated if the update statement does not change any values.

select @@DBTS

0x0000000000012112

    Now let's create a table with rowstore column.

DECLARE @OLTP_Invoices as TABLE (
InvoiceId int PRIMARY KEY,
CustomerId int,
OrderId int,
InvoiceDate datetime,

Comments varchar(100),
rowver rowversion
)

DECLARE @Warehouse_Invoices as TABLE (
InvoiceId int PRIMARY KEY,
CustomerId int,
OrderId int,
InvoiceDate datetime,

Comments varchar(100),
rowver rowversion
)

INSERT INTO @OLTP_Invoices (InvoiceId, CustomerId, OrderId, InvoiceDate)
Select InvoiceId, CustomerId, OrderId, InvoiceDate
FROM Sales.Invoices

INSERT INTO @Warehouse_Invoices (InvoiceId, CustomerId, OrderId,InvoiceDate)
Select InvoiceId, CustomerId, OrderId, InvoiceDate
FROM @OLTP_Invoices

   Now, we have the tables, lets update the OLTP table and check the rowstore value.

select max(rowver) as beforeUpdate from @OLTP_Invoices

UPDATE @OLTP_Invoices set Comments = 'comment is updated' 
where invoicedate between '2013-1-1' and '2013-2-2'

select max(rowver) as afterUpdate from @OLTP_Invoices

 

 
     Here what we have so far, we have two tables, both had the same data, OLTP table got some updates and tables are not synced anymore. We need to find what is changed in OLTP table fast and sync the tables. I am going to use rowstore column to find updated rows. Probably, you are going to do this part in your SSIS package. All we need to do is load both tables and compare the rowstore values. In the following statement, I am getting the latest rowversion from the data warehouse version and compare to OLTP version.

declare @lastchange varbinary(8) = (select max(rowver) from @Warehouse_Invoices)
select * from @OLTP_Invoices where rowver > @lastchange

We got all the updated rows, from this point you can copy these rows to your data warehouse to sync the tables.