Timestamp (timestamp) in SQL Server

The SQL Server timestamp data type is independent of time and date. SQL Server Timestamp is a binary number that indicates the relative order in which data modifications occur in the database. The implementation of the timestamp data type was originally intended to support the SQL Server recovery algorithm. Each time the page is modified, it is marked with the current @ @DBTS value and then @ @DBTS plus 1. This is sufficient to help the recovery process determine the relative order of page modifications, but the timestamp value has no relation to time.

@ @DBTS Returns the last timestamp value used in the current database. When you insert or update a row that contains a timestamp column, a new timestamp value is generated.

SELECT @ @DBTS

Applicable scenarios:

Query the change data through the timestamp field, if the value of the last time stamp saved is: 0x0000000000163e30, then we can get a record of the change after this timestamp by the following SQL script, we can get the data to update the memory data.

Precautions:

1. 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 the database time stamp;

2. A table can have only one timestamp column;

3. Note that deleting a data operation is not a way to record the timestamp, so the logic of deleting the record should be to use a field to identify that the row record has been deleted;

4. This attribute makes the timestamp column unsuitable for use as a key, especially if it is not used as a primary key;

5. If the column belongs to an index key, all updates to the data row will also cause the index to update;

6. To return the current timestamp value for the database: SELECT @ @DBTS

7. In the DDL statement, try to use rowversion instead of timestamp, and there is no rowversion data type when you design the table in SSMs;

8. In the CREATE table or ALTER table statement, you do not have to specify a column name for the timestamp data type, and if you do not specify a column name, the Microsoft SQL Server database engine generates a timestamp column name; rowversi The on synonym does not have this behavior. When you use rowversion, you must specify a column name.

9. The non-nullable rowversion column is semantically equivalent to the binary (8) column. Nullable rowversion columns are semantically equivalent to the varbinary (8) column.

Timestamp (timestamp) in SQL Server