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
Jeff Prom's Data, Database, and SQL Blog 