Karen's SQL Blog

« MVP Ron Talmage on DST, SQL Agent, and Log Shipping | Main | New book on SQL Server Notification Services »

November 05, 2004

Mini-tutorial on timestamp

After posting Ron's discoveries about his discoveries related to Daylight Saving Time and log shipping, I decided to review the timestamp data type and write up a short "mini tutorial" for SQL eXTRA. Here it is:

In SQL Server, the timestamp is a unique 6-byte integer that comes from a single source per database that's automatically incremented whenever anything is changed within that database. Most database objects have the timestamp embedded in them by default; rows in tables don't.

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). A timestamp is used typically as a mechanism for version-stamping table rows.

Notes
▪ 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. As you can guess, this property makes a timestamp column a poor candidate for keys, especially primary keys. As Microsoft explains, "Any update made to the row changes the timestamp value, thereby changing the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. If the column is in an index key, all updates to the data row also generate updates of the index."

▪ Timestamps in a particular database start with the value that was current in the "model" database when the new database was created, and 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 won't help. As Microsoft says, "This is not a major concern because at 100 transactions per second, 2**48 will not wrap for more than 100 years."

▪ T-SQL's timestamp isn't the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the T-SQL datetime data type. Because a future release of SQL Server may modify the behavior of T-SQL's timestamp data type to align it with the behavior defined in the standard, Microsoft has promised that, "At that time, the current timestamp data type will be replaced with a rowversion data type."

▪ Beginning with SQL Server 2000, there's a rowversion synonym for the timestamp data type, and Microsoft recommends using it instead of timestamp wherever possible in DDL statements.

▪ In a CREATE TABLE or ALTER TABLE statement, you don't have to supply a column name for the timestamp data type (if you don't, SQL Server generates a column name of timestamp), but the rowversion data type synonym doesn't follow this behavior, and you must supply a column name when you specify rowversion.

▪ A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column.

▪ Timestamps can't be return types in UDFs and don't support bound defaults or default constraints.

▪ Although you can save nearly any T-SQL data type as a global variable using XPs, that's not the case for bigint, timestamp, cursor, table, uniqueidentifier, or sql_variant.

▪ CURRENT_TIMESTAMP returns the current date and time. This function is equivalent to GETDATE().
See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_cr-cz_60mo.asp for more info.
CURRENT_TIMESTAMP (along with CURRENT_TIME) are reserved keywords in T-SQL, but, curiously, timestamp isn't. See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_9oj7.asp for a list of reserved keywords.

▪ You can use ALTER TABLE to change a column of any data type except text, ntext, image, timestamp, or sql_variant to sql_variant, but you can't use ALTER TABLE to change the data type of an sql_variant column to any other data type.

Potential uses
▪ 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's 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 app wants to issue the update, regardless of the fact that another user has changed the row, it's only necessary to reissue the UPDATE command without the timestamp term in the WHERE clause. Without actually updating the row, the app 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 and is available to the app via the dbtsnewval() function. (If the update is not successful, no timestamp is returned from the server. This feature allows an app 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. But be careful to use such nonrelational techniques only when absolutely necessary!

Related KBs
▪ 44415 INF: Timestamps and Their Uses
▪ 319636 BUG: Date Formats Change to DBTimeStamp After Server Processes Cube
▪ 306625 INF: Last Batch Date is Seen as 1900-01-01 00:00:00.000
▪ 327080 PRB: SQL Server datetime Values Are Randomly Rounded Up in ADO Application
▪ 813524 OLE DB Conversion Errors May Occur After You Select a Literal String That Represents Datetime Data as a Column
▪ 319636 BUG: Date Formats Change to DBTimeStamp After Server Processes Cube
▪ 311801 BUG: Error 3154 Reported in Log Shipping Restore Job Sporadically
▪ 321852 DOC: Loopback_detection Option is Not Clearly Explained in SQL Server Books Online
▪ 834604 Transactional replication for text or for image data may not work with DB-Library apps that use the dbtxptr function in SQL Server 7.0 Service Pack 2 or in SQL Server 2000
▪ 104829 INFO: Converting Binary Data to Hexadecimal String

And finally, feel free to read Alexander Kozak's May 2004 SQL Server Professional article at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro04/html/sp04e9.asp!

Posted by karen at November 5, 2004 12:31 PM

Comments

Posted by: Dan at December 19, 2005 05:58 PM

Posted by: Dawn O'Shaughnessy at January 24, 2006 12:54 PM

Posted by: Dawn O'Shaughnessy at January 24, 2006 12:59 PM

Post a comment

Name:

Email Address:

URL:
Remember Me? YesNo

Comments: