Tuesday, February 26, 2013

SQL Server-RowVersion

In SQL Server one of the recent additon has been change data capture a very useful and a powerful functionality to capture data changes in sql server. One of the other sdditions has been a column datatype called rowversion. The column type rowversion usually is used for situations where one needs to capture deltas with respect to the data. When i use the term delta what i mean is that let us take a scenario: Intially say we load 100 records into a table and then in the next day load around 10 records change, we could use the  rowversion column to identify the rows that changed. What happens is that when a record is inserted or updated the rowversion value changes. Rowversion column type would eventually replace the timestamp column type in sql server. In case data is pulled from a source which is sql server based, the rowversion column type could be added to the source tables in oder to track which records changed. Here is a sample code which can be used: I used MSDN as a reference to get the sample code:


,myValue int, RV rowversion);

INSERT INTO MyVer (myKey, myValue) VALUES (1, 0);
INSERT INTO MyVer (myKey, myValue) VALUES (2, 0);
Here in the sample code above one can see that the column RV is defined as a rowversion data type.  As per MSDN:
Rowversion Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type.

Once the table is created and data has been inserted, we can run the following statement and look at the values of the RV column:

myKey myValue RV

1          0           0x0000000000000A36

2          0            0x0000000000000A37

Now when the value of the column myValue is update in the table, the value of RV will change. In order to determine the latest value of rowversion in a database , one can use @@DBTS.

One can use the following code, to implement Optimistic concurrency control.

DECLARE @t TABLE (myKey int);

SET myValue = 2
OUTPUT inserted.myKey INTO @t(myKey)
WHERE myKey = 1
AND RV = myValue;

RAISERROR ('error changing row with myKey = %d'
,16 -- Severity.
,1 -- State
,1) -- myKey that was changed


In Summary the rowversion datatype can be used to track changes in the data in sql server.

No comments:

Post a Comment