Books that are online in SQL Server say this:
The SQL Server timestamp data type is independent of time and date. SQL Server Timestamp is a binary number that indicates the relative order in which data modifications occur in the database. The timestamp data type was implemented initially to support the SQL Server recovery algorithm. Each time the page is modified, it is marked with the current @ @DBTS value, and then @ @DBTS plus 1. This is sufficient to help the recovery process determine the relative order of page modifications, but the timestamp value has nothing to do with time.
I'll explain it in words:
We know that the number type has an identity attribute, set "Identity Seed", "identity increment", each additional record, the value of this field will automatically increase on the basis of a recent identity value, so that we can know which records are added first, which records are added after the But we have no way of knowing which records have been modified. Timestamp this type of field, every time you add a record, it automatically increases on the basis of a recent timestamp, and when you modify a record, it automatically increases on the basis of a recent timestamp, so we know which records have been modified.
Timestamp This data type shows the number of automatically generated binaries, ensuring that these numbers are unique in the database. Timestamp is generally used as a mechanism for adding a version stamp to a table row. The storage size is 8 bytes.
Comments
The Transact-SQL timestamp data type differs from the timestamp data type defined in the SQL-92 standard. SQL-92 timestamp data types are equivalent to Transact-SQL datetime data types.
A future version of Microsoft®sql Server™ may modify the behavior of the Transact-SQL timestamp data type so that it is consistent with the behavior defined in the standard. By then, the current timestamp data type will be replaced with the rowversion data type.
Microsoft®sql server™2000 introduced rowversion synonyms for the timestamp data type. Use rowversion as much as possible in DDL statements without using timestamp. RowVersion is restricted by the behavior of data type synonyms. For more information, see Data type synonyms.
In a CREATE table or ALTER table statement, you do not have to provide a column name for the timestamp data type:
CREATE TABLE exampletable (prikey int PRIMARY KEY, timestamp) if the column name is not supplied, SQL Server generates the timestamp column name. rowversion data type synonyms do not have such behavior. Column names must be supplied when specifying rowversion.
A table can have only one timestamp column. The values in the timestamp column are updated each time the row containing the timestamp column is inserted or updated. This property makes the timestamp column unsuitable for use as a key, especially when it is not used as a primary key. Any update to the row changes the timestamp value, which changes the key value. If the column belongs to a primary key, the old key value will not be valid, and the foreign key referencing the old value will no longer be valid. If the table is referenced in a dynamic cursor, all updates change the position of the row in the cursor. If the column is an index key, all updates to the data row will also cause an index update.
Timestamp columns that are not nullable are semantically equivalent to the binary (8) column. Nullable timestamp columns are semantically equivalent to the varbinary (8) column.
Microsoft SQL Server Stores the value of a datetime data type within two 4-byte integers. The first 4 bytes stores the number of days before or after base date (i.e. January 1, 1900). The base date is the system reference date. DateTime values older than January 1, 1753 are not allowed. Another 4-byte store is the daily time represented by the number of milliseconds after midnight.
Copy Code code as follows:
@ @DBTS (SELECT @ @DBTS)
Returns the value of the current timestamp data type for the current database. This timestamp value is guaranteed to be unique in the database.
Grammar
@ @DBTS
return type
varbinary
Comments
@ @DBTS
Returns the timestamp value used at the end of the current database. When a row with a timestamp column is inserted or updated, a new timestamp value is generated.
