Hence, TIMESTAMP/ROWVERSION provides a row-versioning
feature in the table. Row-versioning can be used to examine the changes in
table. It can also help to manage the synchronization in multiuse access. For
example, if two users, A and B, are accessing a table T at the same time. Both
are intended to make some changes in a set of rows in T by using the latest values
available in that. Both set A and B can read the required values from rows with
the TIIMESTAMP column values. Before going to update the value in specific row
in the table, if both A and B checks if the already read TIMESTAMP value still
matches the TIMESTAMP value of the same row in the table, then they can go for
update. If the TIMESTAMP value differs then it means another user has already modified
that row, so it requires reading that table (at least modified rows) again to
get the updated values. In this case, both user A and B are synchronized to
each other.
Few more operations with TIMESTAMP
In general we do not need to update a timestamp column value
manually, but if we need to do it then we can use binary and varbinary values
to update a timestamp column value.
Though you can use integer values to assign in timestamp
type variable in SQL. For example:
Listing 9
DECLARE @timest AS TIMESTAMP
SET @timest = 5
SELECT @timest
If you execute the above query this will result following output:
0x0000000000000005
Now if you try following a query to insert a row with
specific timestamp value in the above created table:
Listing 10
INSERT INTO MyTestTable([TIMESTAMP],[SmallDateTime],[DateTime])
VALUES(5,GETDATE(), GETDATE())
it will show the following error message:
Cannot insert an explicit value into a
timestamp column. Use INSERT with a column list to exclude the timestamp
column, or insert a DEFAULT into the timestamp column.
As the error message indicates, we can either remove the
timestamp column from the insert statement or set DEFAULT keyword as the value
to automatic timestamp value to be inserted.
Listing 11
INSERT INTO MyTestTable1([TIMESTAMP],[SmallDateTime],[DateTime])
VALUES(DEFAULT,GETDATE(), GETDATE())
