By Kalman Toth, M.Phil., M.Phil., MCDBA

February 14, 2005

A table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated. Of course this means we should not use it as a primary key, because we can get many orphans quickly if updates are performed on other columns. As a row is modified in a table, the timestamp is updated with the current database timestamp value obtained from the @@DBTS function.

Timestamp is the wrong name, quite confusing in fact.. It has nothing to do with time. Microsoft will rename it rowversion in the future. Rowversion is the synonym for timestamp in SQL Server 2005.

It is an 8 bytes unique binary key within the database.

Here is how it looks like: 0x0000000000000743. After an update to the row: 0x0000000000000745.

So how can we use it?

The main purpose is row versioning in multi user environment.
Assume you are a developer and developing a program in Visual Basic to update the name and address table of customers. There will be 100 staff member who can perform this application function. How can you be sure that while staff A typing in the change, staff X is not changing the same row?

Here is what you do:
1. Read the name and address table including the timestamp. You display the info to the user for update and save the timestamp.
2. Certain amount of time later, like 2 minutes, the user presses the submit button after changes were typed in.
3. You open a transaction with Begin Transaction
4. You read the timestamp of the name and address row
5. You compare the current timestamp to the saved timestamp.
6. If the timestamps are same, you update the row and commit the transaction
7. If timestamps are difference, you roll back the transaction and notify the user about the fact that the data was changed by someone else. You can let the user decide what to do.

This is pretty common practice in multi user environment. The alternate would be to examine a datetime column, or the entire row.