[Sqlserver] timestamp Timestamp

Http://xthanks.blog.163.com/blog/static/7891148201131545556817/

 

Timestamp
The SQL Server timestamp data type is independent of the time and date. SQL Server timestamp is
Binary Number, which indicates the relative sequence of data modification in the database. The timestamp data type was initially implemented to support the SQL Server restoration algorithm. Each modification page
The current @ dbts value is used to mark it once, and then @ dbts is added with 1. This is enough to help the recovery process determine the relative order of page modifications,
The timestamp value has no relationship with the time.
In SQL Server 7.0
And SQL Server 2000, @ dbts will increase progressively only when used in the timestamp column. If a table contains a timestamp column,
When a row is modified by the insert, update, or delete statement, the timestamp value of this row is set to the current @ dbts value.
Add 1 to @ dbts.
Because the timestamp value changes during each modification, do not use the timestamp column in the key (especially the primary key.
To record the number of data changes in the table, you can use the datetime or smalldatetime data type to record these events and triggers. In this way, when a change occurs, the values can be automatically updated.

Specifies the Data Type of the unique binary number automatically generated in the public database. Timestamp is usually used for tables
Row and version stamp
. The storage size is 8 bytes. The timestamp data type is only an incremental number and does not retain the date or time.
. To record the date or time, use the datetime data type.

Remarks
Each
Each database has a counter. When you insert or update a table that contains the timestamp column in the database, the counter value increases. This counter is the database timestamp. This works.
Trace the relative time in the database, rather than the actual time associated with the clock. A table can have only one timestamp column. Each time you modify or insert a row containing the timestamp Column
The incremental database timestamp value is inserted in the timestamp column. This attribute makes the timestamp column not suitable for use as a key, especially not as a primary key. Any update to the row changes the timestamp value.
To change the key value. If the column belongs to the primary key, the old key value is invalid, and the foreign key that references the old value is no longer valid. If the table is referenced in a dynamic cursor, all updates change the row position of the cursor. If this column belongs to the index key, all updates to the data row will also lead to index updates.

Enable
Using the timestamp column in a row can easily determine whether any value in the row has changed since the last read. If the row is changed, the timestamp value is updated. If
If no changes are made to the row, the timestamp value will be consistent with the timestamp value of the row that was previously read. To return the current timestamp value of the database, use @ dbts
.

The Transact-SQL timestamp data type is different from the timestamp data type defined in the SQL-2003 standard. The SQL-2003 timestamp data type is equivalent to the transact-SQL datetime data type.

Rowversion is a synonym of the timestamp data type and acts as a synonym of the data type. In DDL statements, use rowversion instead of timestamp. For more information, see Data Type Synonyms (TRANSACT-SQL ).

In the create table or alter table statement, you do not need to specify a column name for the timestamp data type. For example:

Create Table exampletable (prikey int primary key, timestamp );
 
If no column name is specified, the Microsoft SQL Server 2005 database engine generates a timestamp column name, but the rowversion synonym does not. When rowversion is used, the column name must be specified.

Note:
Duplicate timestamp values may be generated when you use a select into statement with a timestamp column in the select list. We recommend that you do not use timestamp in this way.

The timestamp Column cannot be empty. It is semantically equivalent to the binary (8) column. The empty timestamp column can be semantically equivalent to the varbinary (8) column.

# Use the timestamp to update local data from the server.