SQL time Stamp

The concept of time stamp has been vague, I believe a lot of friends will be mistaken: Timestamp is a Time field, each time you add data, fill in the current time value. In fact, this misled a lot of friends.
1. Basic Concepts

Timestamp: A unique binary number automatically generated in the database, independent of time and date, usually used as a mechanism for adding a version stamp to a table row. The storage size is 8 bytes.

Each database has a counter that is incremented when an INSERT or update operation is performed on a table that contains timestamp columns in the database. The counter is a database timestamp. This can track the relative time within the database, not the actual time associated with the clock. A table can have only one timestamp column. The incremental database timestamp value is inserted in the timestamp column each time the row that contains the timestamp column is modified or inserted. This property makes the timestamp column unsuitable for use as a key, especially if it is not used as a primary key. Any update to the row changes the timestamp value, thereby changing the key value. If the column belongs to a primary key, the old key value will be invalid, 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 belongs to an index key, all updates to the data row will also cause the index to update.

Using the timestamp column in a row makes it easy to determine whether any value in the row has changed since the last time it was read. If a change is made to the row, the timestamp value is updated. If no changes are made to the row, the timestamp value is consistent with the timestamp value of the row that was previously read. To return the current timestamp value for the database, use @ @DBTS.




2. The role of timestamps

Play a role when controlling concurrency:
User A/b open a record at the same time to start editing, save is able to determine the timestamp, because the record is updated each time the system will automatically maintain the timestamp, so if the save time to find the timestamp is not equal to the timestamp in the database, indicating that the record was updated in this process, This will prevent others from being overwritten by the update.

3. Application of Timestamps

To put it simply, timestamp mainly records the last modification timestamp of the line, noting that the timestamp is not converted to time, only that the line has been modified.

What's the use of it? It is usually used for data incremental updates, for example, I copy data from this table to another table, but if I want to copy only the updated, then the maximum timestamp value is recorded from the last update, and then the current update, as long as the where condition finds the one that is greater than the last update. Timestamp the value of all rows. It then extracts the updated data and replicates it to another server, which is the incremental update.

4. Examples of applications in SQL

(Microsoft's recommendation-timestamp syntax has been superseded, in DDL statements, try to use rowversion instead of timestamp.) Future versions of Microsoft SQL Server will remove this feature.

See http://msdn.microsoft.com/zh-cn/library/ms182776.aspx)

Rowsversion is timestamp.

Workarounds for missing updates

Lost update concept: When the user modifies a row of data at the same time, they first read the data, put it in front of the modification, when modified, then submit the data, so that the last submitted data will overwrite the previously submitted data, resulting in the loss of updates.

Long story short, describes how to prevent missing updates:

Use the rowsversion timestamp.

Each time the update, MSSQL will automatically update the value of the rowversion, if the line before the read and before the value of the update before and after inconsistent, it means that there are other transactions updated this column, so that the column can not be updated, so as to prevent the loss of updates.

Example: Declare table tmp (a varchar (ten), B rowsversion)

INSERT INTO TMP (a) VALUES (' abc ')

Transaction A:

DECLARE @rv rowversion

Select @rv =b from TMP where a= ' abc '

WAITFOR DELAY ' 00:00:05 '

Update tmp set a= ' xyz ' where [email protected]

Transaction B:

Update tmp set a= ' AAA ' where [email protected]

Transaction A will find that the ' AAA ' is not erased after execution, thus preventing the loss of updates.

SQL time Stamp