Jeff Prom's SQL Server Blog

Sharing knowledge and tips on SQL Server, Business Intelligence and Data Management.

Using RowVersion and Timestamp

Posted by jeffprom on October 5, 2011

If you do any kind of batch ETL processing, it can be very useful to know if any records in your database have been updated. One way to do this is to use a rowversion or timestamp column in your tables.

Books online has a good definition for timestamp:
Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database.
This counter is the database timestamp. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one timestamp column. This property makes a timestamp column a poor candidate for keys, especially primary keys. Any update made to the row changes the timestamp value and, therefore, changes the key value.

Below are a couple of examples I came up with to demonstrate their potential. The first set uses timestamp. The second set uses rowversion. However, rowversion still uses timestamp behind the scenes so it’s not much different.


---------------------------------------
-- TIMESTAMP
---------------------------------------
-- 1. create a test table and put a few records in it
CREATE TABLE Test_TimeStamp (RowID int PRIMARY KEY, Value int, TS timestamp);
GO
INSERT INTO Test_TimeStamp (RowID, Value) VALUES (1, 0);
GO
INSERT INTO Test_TimeStamp (RowID, Value) VALUES (2, 0);
GO
INSERT INTO Test_TimeStamp (RowID, Value) VALUES (3, 0);
GO

-- 2. store the latest timestamp that is currently in the table
DECLARE @TS AS timestamp;
SET @TS = (SELECT @@DBTS AS TS)
--SELECT @TS

-- update a couple of records
UPDATE Test_TimeStamp SET Value=2 WHERE RowID=1
UPDATE Test_TimeStamp SET Value=3 WHERE RowID=2

-- show all of the records that have changed
SELECT * FROM Test_TimeStamp WHERE TS > @TS

-- show the new latest timestamp stored in the table
SELECT @@DBTS AS TS

-- 3. re-run step 2 and see the changes again and again
--DROP TABLE Test_TimeStamp
--GO

---------------------------------------
-- ROWVERSION
---------------------------------------
-- 1. create a test table and put a few records in it
CREATE TABLE Test_RowVersion (RowID int PRIMARY KEY, Value int, RV rowversion);
GO
INSERT INTO Test_RowVersion (RowID, Value) VALUES (1, 0);
GO
INSERT INTO Test_RowVersion (RowID, Value) VALUES (2, 0);
GO
INSERT INTO Test_RowVersion (RowID, Value) VALUES (3, 0);
GO

-- 2. store the latest timestamp that is currently in the table
DECLARE @TS AS timestamp;
SET @TS = (SELECT @@DBTS AS TS)
--SELECT @TS

-- update a couple of records
UPDATE Test_RowVersion SET Value=2 WHERE RowID=1
UPDATE Test_RowVersion SET Value=3 WHERE RowID=2

-- show all of the records that have changed
SELECT * FROM Test_RowVersion WHERE RV > @TS

-- show the new latest timestamp stored in the table
SELECT @@DBTS AS TS

-- 3. re-run step 2 and see the changes again and again
--DROP TABLE Test_RowVersion
--GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: