Timestamp and Rowversion in sql

Rowversion can be used to prevent overlapping updates but requires the capturing of the rowversion column in selects, tracking of the rowversion by
the application, and including the tracked rowversion in the where clause of all update and delete statements.

In conclusion, use the rowversion datatype only when overlapping update logic will be included in the application architecture.


Timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database.
Timestamp data is not related to the date and time of an insert or change to data.

MS wants us to use the datatype called rowversion as its a better description of what it is. All that timestamp/rowversion will get you is a unique id when the row was updated/inserted.

Example of usage:
Optimastic concurrency.
Data Warehouse loads.

Basically they compare an old record to the existing, if there is a difference in the timestamp/rowversion column, then they know the row as been modified. Nothing to do with the actual time.
Rowversion cannot be used to determine the data and time when the row was last updated.
There are a handful of differences between the ROW VERSION and TIMESTAMPS columns. Yet, most accidental DBAs and novice database developers make the mistake of thinking that they are one and the same. While I had mentioned all the differences in my post on optimistic concurrency, here’s a recap:

1. The ROWVERSION data type is just an incrementing number and does not preserve a date or a time
2. A table can have only one ROWVERSION column
3. ROWVERSION should not be used as keys, especially primary keys
4. Using a SELECT INTO statement has the potential to generate duplicate ROWVERSION values
5. Finally for those who came from SQL 2005, the TIMESTAMP is deprecated. Please avoid using this in new DDL statements
6. Unlike TIMESTAMP, the ROWVERSION column needs a column name in the DDL statements

The last difference is what throws off most people – they simply replace all instances of “TIMESTAMP” in their DDL code and expect it to work – which will not be the case.
Here’s a simple demonstration.
A typical TIMESTAMP implementation would look like this:

01.USE tempdb

02.GO

03. 

04.--Safety Check

05.IF OBJECT_ID('RowVersionDemo') IS NOT NULL

06.BEGIN

07.DROP TABLE RowVersionDemo

08.END

09.GO

10. 

11.--Create a test table

12.CREATE TABLE RowVersionDemo (Id INT IDENTITY(1,1),

13.Data VARCHAR(20),

14.TIMESTAMP

15.)

16.GO

17. 

18.--Insert some test data

19.INSERT INTO RowVersionDemo (Data) VALUES ('Windows'),

20.('SQL Server')

21.GO

22. 

23.--Fetch Data

24.SELECT RowVersionDemo.Id, RowVersionDemo.Data, RowVersionDemo.TIMESTAMP

25.FROM RowVersionDemo

As you can see, we just placed a TIMESTAMP column in the table definition – without really specifying the column name. Simply replacing all instances of TIMESTAMP with ROWVERSION will result in an error:

01.--ROWVERSION - ERROR

02.USE tempdb

03.GO

04. 

05.--Safety Check

06.IF OBJECT_ID('RowVersionDemo') IS NOT NULL

07.BEGIN

08.DROP TABLE RowVersionDemo

09.END

10.GO

11. 

12.--Create a test table

13.CREATE TABLE RowVersionDemo (Id INT IDENTITY(1,1),

14.Data VARCHAR(20),

15.ROWVERSION

16.)

17.GO

Msg 173, Level 15, State 1, Line 5
The definition for column 'ROWVERSION' must include a data type.
ROWVERSION is a data-type and needs a column name to be associated with it. Hence, the following will work:

01.--ROWVERSION - FIXED

02.USE tempdb

03.GO

04. 

05.--Safety Check

06.IF OBJECT_ID('RowVersionDemo') IS NOT NULL

07.BEGIN

08.DROP TABLE RowVersionDemo

09.END

10.GO

11. 

12.--Create a test table

13.CREATE TABLE RowVersionDemo (Id INT IDENTITY(1,1),

14.Data VARCHAR(20),

15.DataVersion ROWVERSION

16.)

17.GO

18. 

19.--Insert some test data

20.INSERT INTO RowVersionDemo (Data) VALUES ('Windows'),

21.('SQL Server')

22.GO

23. 

24.--Fetch Data

25.SELECT RowVersionDemo.Id, RowVersionDemo.Data, RowVersionDemo.DataVersion

26.FROM RowVersionDemo

The above simple demonstration proves that TIMESTAMP and ROWVERSION cannot be used in the same way when defining the database tables. Conversion from TIMESTAMP to ROWVERSION is not a direct find-replace operation.