ID Number: Q44415
1.00 1.10 1.11 4.20
OS/2
Summary:
In SQL Server, the timestamp is a unique 6-byte integer that comes
from a single source per database, and is automatically incremented
whenever anything is changed within that database. Most database
objects have the timestamp embedded in them by default; rows in tables
do not. Timestamps can be added to tables by including a column with
the datatype "timestamp." Timestamps in rows and during internal
manipulation are 8-byte variable-length binary fields that contain
the 6-byte timestamp value, which was current when the row was last
inserted or updated. When a row is updated, the timestamp in the row
will appear to be incremented by more than one because several other
objects are implicitly updated during the row update (page timestamp,
log timestamp, related index timestamps, and so forth).
Initial Value
-------------
Timestamps in a particular database start with the value that was
current in the "model" database when the new database was created,
because new databases are actually "cloned" from the "model" database.
Maximum Value
-------------
Timestamps increase until the maximum value that can be stored in 6
bytes (2**48) is reached. When this maximum is reached, the database
will not permit any more updates.
In SQL Server version 1.0, there is no warning, other than the
value of the last timestamp. In versions of SQL Server later than
1.0 (versions 1.1, 1.11, and 4.2), a 935 warning message is
generated when there are only 1,000,000 timestamp values left in the
database.
The only way to start over is to copy out all of the data with BCP
and to re-create the database; dumping and restoring will not help.
This is not a major concern because at 100 transactions per second,
2**48 will not wrap for more than 100 years.
Usage
-----
Timestamps can be used in place of locking to prevent update
collisions when multiple users browse through the same table,
performing occasional updates. Timestamps allow more concurrency than
locking because no rows are actually locked, except for uncommitted
updates.
Browse mode uses timestamps by constructing a WHERE clause on the
UPDATE statement that includes the value of the timestamp. If a second
user updates the row after the first user has read it, but before the
first user has updated it, the timestamp term in the WHERE clause will
cause the update to return "0 rows affected." If this happens, it is
up to the application to decide what to do. One option is to show a
message and the new value of the row, and allow the user to try again.
If the application wants to issue the update, regardless of the fact
that another user has changed the row, it is only necessary to
reissue the UPDATE command without the timestamp term in the WHERE
clause.
Without actually updating the row, the application can test whether
another user has updated the row by issuing a SELECT with the same
WHERE clause, as would have been used on an UPDATE. If you get the
message "not found," someone has changed the row.
The new timestamp is returned to the client as a by-product of the
update. It is available to the application via the dbtsnewval()
function. If the update is not successful, no timestamp is returned
from the server. This feature allows an application to perform
multiple updates on the same row without having to issue a redundant
read to get the new timestamp value.
Because timestamps are guaranteed to always increase within a
database, they can act as unique (although not sequential) row
identifiers. Be careful to use such nonrelational techniques only
when absolutely necessary.