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:
CREATE TABLE MyVer (myKey int PRIMARY KEY
,myValue int, RV rowversion);
GO
INSERT INTO MyVer (myKey, myValue) VALUES (1, 0);
GO
INSERT INTO MyVer (myKey, myValue) VALUES (2, 0);
GO
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);
UPDATE MyTest
SET myValue = 2
OUTPUT inserted.myKey INTO @t(myKey)
WHERE myKey = 1
AND RV = myValue;
IF (SELECT COUNT(*) FROM @t) = 0
BEGIN
RAISERROR ('error changing row with myKey = %d'
,16 -- Severity.
,1 -- State
,1) -- myKey that was changed
END
In Summary the rowversion datatype can be used to track changes in the data in sql server.
