Shulou(Shulou.com)05/31 Report--

SQL Server how to achieve timestamp function, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

1. Basic concept

Timestamp: a unique binary number automatically generated in a database, independent of time and date, usually used as a mechanism for stamping table rows. 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 in the database that contains a timestamp column. The counter is a database timestamp. This tracks the relative time within the database instead of the actual time associated with the clock. A table can have only one timestamp column. Each time a row containing a timestamp column is modified or inserted, an incremental database timestamp value is inserted in the timestamp column. This attribute makes the timestamp column unsuitable for use as a key, especially as a primary key. Any update to the row changes the timestamp value, which changes the key value. If the column belongs to the primary key, the old key value will be invalid and the foreign key that references the old value will no longer be valid. If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. If the column belongs to an index key, all updates to the data row will also result in index updates.

Using the timestamp column in a row, you can easily determine whether any value in that row has changed since it was last read. If changes are made to the row, the timestamp value is updated. If no changes are made to the row, the timestamp value is consistent with the previous timestamp value when the row was read. To return the current timestamp value of the database, use @ @ DBTS.

two。 The role of timestamps

Plays a role in controlling concurrency:

User Aswab opens a record to start editing at the same time, and the timestamp can be judged when the record is saved, because each time the record is updated, the system automatically maintains the timestamp, so if the timestamp taken out is not equal to the timestamp in the database, it means that the record has been updated during this process, which can prevent other people's updates from being overwritten.

3. Application of timestamp

To put it simply, timestamp mainly records the timestamp of the last modification of the line. Note that this timestamp cannot be converted into time, but can only be marked as modified by the line.

What's the use? It is usually used for incremental data updates, for example, I copy data from this table to another table, but if I want to copy only what has been updated, then record the maximum timestamp value from the last update, and then in the current update, as long as the where condition finds all rows greater than the timestamp value of the last update. Then extract the updated data and copy it to another server, which is used for incremental updates.

4. An example of application in SQL

(Microsoft's suggestion-timestamp syntax has been replaced, in DDL statements, please 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)

Rowversion is timestamp.

The solution to missing updates is as follows:

Lost update concept: when users modify a row of data at the same time, they read the data first, put it on the front end for modification, and then submit the data, so that the last submitted data will overwrite the previously submitted data, resulting in the loss of updates.

To make a long story short, introduce how to prevent the loss of updates: use rowversion timestamps. Each time you update, mssql automatically updates the value of rowversion. If the value of a row before reading is inconsistent with that before updating, it means that another transaction has updated the column, so that the column can not be updated, thus preventing 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: (new query executes the following code)

Declare @ rv rowversionselect @ rv=b from tmp where a='abc'waitfor delay'00 update tmp set 05'-take a break for 5 seconds

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

Will it be helpful for you to read the above content after reading declare @ rv rowversionselect @ rv=b from tmp where a='abc'update tmp set aaaaaa'where b=@rvgo? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.