SQL Server timestamp features and usage details _mssql

The examples in this article describe the SQL Server timestamp feature and usage. Share to everyone for your reference, specific as follows:

The concept of timestamp has been blurred, I believe that a lot of friends will also be mistaken: The 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 that is automatically generated in the database, regardless of time and date, and is often 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 keeps track of the relative time in the database, not the actual time associated with the clock. A table can have only one timestamp column. Each time you modify or insert a row that contains a timestamp column, the incremental database timestamp value is inserted in the timestamp column. 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.

Using the timestamp column in a row makes it easy to determine whether any of the values in the row have changed since the last read. If you make a change 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 of the database, use the @ @DBTS.

2. The role of the time stamp

Plays a role in controlling concurrency:

User A/b opens a record at the same time to edit, save is to determine the timestamp, because every time the record is updated, the system will automatically maintain the timestamp, so if you save the time stamp is found to be not equal to the time stamp in the database, indicating that the record has been updated in this process, This will prevent others from being overwritten.

3. Application of time stamp

To put it simply, the timestamp is primarily a record of the last modification timestamp of the line, noting that the timestamp is not convertible to time and can only be annotated with a modified line.

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

4. Examples of applications in SQL

(Microsoft's advice--timestamp syntax has been replaced, 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)

RowVersion is timestamp.

The workaround for missing updates is as follows:

Lost update Concept: When users modify a row of data at the same time, they first read the data, put on the front to make changes, when modified, then submit the data, so that the final submission of the data will overwrite the previously submitted data, which caused the loss of the update.

Long story short, describes how to prevent loss of updates by using a rowversion timestamp.
Every time you update, MSSQL automatically updates the value of the rowversion, if the row before and after the update before the value of the inconsistent, it indicates that there are other transactions updated this column, so you can not update this column, thereby preventing the loss of updates.

Example:

Create a table first:

DECLARE table tmp (a varchar (a), B rowsversion)
INSERT INTO TMP (a) VALUES (' abc ') 

Transaction A: (new query executes the following code)

DECLARE @rv rowversion
select @rv =b from TMP where a= ' abc '
WAITFOR DELAY ' 00:00:05 '--rest 5 Seconds
update tmp set a= ' x YZ ' where b= @rv go

Transaction B: (new query executes the following code)

DECLARE @rv rowversion
select @rv =b from TMP where a= ' abc '
update tmp set a= ' AAA ' where b= @rv
go

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

PS: About timestamps Here's a Unix timestamp online conversion tool, with instructions on the Unix timestamp, as well as Java, Javascript, MySQL, SQL Server, PostgreSQL, PHP and other programming languages and database timestamp use skills:

Unix timestamp (timestamp) conversion tool:
Http://tools.jb51.net/code/unixtime

More about SQL Server-related content readers can view the site topics: SQL Server stored procedure tips, SQL Server query operations tips, SQL Server indexing operations tips, SQL Server Paging technology summary and SQL Server Common Functions Summary

I hope this article will help you with your SQL Server database program.