SQL Server timestamp functions and Usage Details, SQL Server

SQL Server timestamp functions and Usage Details, SQL Server

This example describes the SQL Server timestamp function and usage. We will share this with you for your reference. The details are as follows:

I have always been vague about the concept of timestamp. I believe many of my friends will also mistakenly think that the timestamp is a time field. Each time you add data, fill in the current time value. In fact, this misleads many friends.

1. Basic Concepts

Timestamp: the unique binary number automatically generated in the database, which is irrelevant to the time and date. It is usually used to add a version stamp to table rows. The storage size is 8 bytes.

Each database has a counter. When you insert or update a table containing the timestamp column in the database, the counter value increases. This counter is the database timestamp. This can track 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 updates to a row change 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.

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 no changes are made to the row, the timestamp value will be consistent with the timestamp value previously read from the row. To return the current timestamp value of the database, use @ DBTS.

2. Functions of Timestamp

It plays a role in controlling concurrency:

User A/B opens A record at the same time to start editing. Saving can determine the timestamp, because the system automatically maintains the timestamp every time the record is updated, therefore, if the timestamp obtained during saving is not the same as the timestamp in the database, it indicates that the record has been updated during this process, so as to prevent updates from being overwritten by others.

3. timestamp Application

To put it simply, timestamp is mainly used to record the last modified timestamp of the row. Note that this timestamp cannot be converted to time and can only be marked as modified.

What is the purpose? It is usually used for incremental data updates. For example, I want to copy data from this table to another table, but if I want to only copy the updated data, then from the last update, record the maximum timestamp value. Then, during the current update, as long as the where condition finds all rows that are greater than the timestamp value of the last update. Then extract the updated data and copy it to another server. This is used for incremental updates.

4. SQL application example

(Microsoft's suggestion-the timestamp syntax has been replaced. In DDL statements, use rowversion instead of timestamp as much as possible. This feature will be removed in future Microsoft SQL Server versions.

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

Rowversion is timestamp.

The solution to missing updates is as follows:

Concept of Missing Update: when users modify a row of data at the same time, they first read the data and put it on the front-end for modification. After the modification, they submit the data, in this way, the last submitted data will overwrite the previously submitted data, resulting in loss of updates.

To put it short, this article introduces how to prevent missing updates: Use the rowversion timestamp.
During each update, mssql automatically updates the value of rowversion. If a row is inconsistent with the value before the update, it indicates that other transactions have updated this column, this prevents the loss of updates.

Example:

Create a table first:

declare table tmp(a varchar(10),b rowsversion)insert into tmp(a) values( 'abc') 

Transaction A: (create A new query and execute the following code)

Declare @ rv rowversionselect @ rv = B from tmp where a = 'abc' waitfor delay' 00: 00: 05 '-- resting for 5 seconds update tmp set a = 'xyz' where B = @ rvgo

Transaction B: (create a new query and execute the following code)

declare @rv rowversionselect @rv=b from tmp where a='abc'update tmp set a='aaa' where b=@rvgo

Transaction A does not erase 'aaa' after execution, which prevents the loss of updates.

PS: Here is a Unix timestamp online conversion tool, with a Unix timestamp description, java, Javascript, MySQL, SQL Server, PostgreSQL, PHP, and other programming languages and database timestamp usage skills:

Unix timestamp Conversion Tool:
Http://tools.jb51.net/code/unixtime