INF: Timestamps and Their Uses

ID: Q44415

The information in this article applies to:

Microsoft SQL Server versions 4.2x, 6.0, 6.5, 7.0

SUMMARY

In Microsoft 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).

MORE INFORMATION

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.

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.

Use

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.

Keywords : kbnetwork SSrvGen
Version : 4.20
Platform : WINDOWS
Issue type :