What does TimeStamp/Rowversion does?

Rowversion /Timestamp is a datatype which stores 8byte binary number. Each time any insert/update operation performed on a table having Rowversion column, the value of row version for inserted or updated rows changes to the latest counter of Rowversion.

A table can have maximum of only one column as rowversion datatype.

Timestamp is synonym to the Rowversion data type.

Lets see with an example, how we can use rowversion as a column in our table.

create table RowVersion_TB

(col1 int, col2 int, timestamp);

Command(s) completed successfully.

Now we have our table created with  rowversion datatype as one of the column. Lets insert few records in it.

insert into RowVersion_TB (col1, col2)

select 1,100 union all

select 2,200 union all

select 3,300

Lets query the table to see what values appears in the rowversion field.

select * from RowVersion_TB

col1        col2        timestamp

----------- ----------- ------------------

1           100         0x00000000000007D4

2           200         0x00000000000007D5

3           300         0x00000000000007D6

(3 row(s) affected)

Alright , the value of rowversion column is in increamented order. Lets update a row say where col1=3 and see how it affect rowversion data.

update RowVersion_TB

set col2=400

where col1=3

select * from RowVersion_TB

col1        col2        timestamp

----------- ----------- ------------------

1           100         0x00000000000007D4

2           200         0x00000000000007D5

3           400         0x00000000000007D7

(3 row(s) affected)

See the rowversion value changed when we change the data of col2 to 07D7

Lets again update the first row of the table to see, the changed value of Rowversion

update RowVersion_TB

set col2=10

where col1=1

select * from RowVersion_TB

col1        col2        timestamp

----------- ----------- ------------------

1           10          0x00000000000007D8

2           200         0x00000000000007D5

3           400         0x00000000000007D7

(3 row(s) affected)

Ah, notice that col1 1 has most updated Rowversion value. So most recently updated record has latest rowversion information.

Coder always have reason to avoid outing and shoping! J