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 to support the SQL Server recovery algorithm. Every time a page is modified, the current @is used @Mark it once with the 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 there is a Timestamp When a row of a column is inserted or updated, a new timestamp value is generated. If it is multiple rows, multiple consecutive timestamp values are generated.
Select@ @DBTS.
Applicable scenarios: .
By querying the change data through the timestamp field, if the value of the last saved timestamp is: 0x00000000163E30, then we can obtain the record of changes after this timestamp through the following SQL script. Once we obtain this data, we can update the memory data.
Timestamp and rowversion .
Timestamp and rowversion are data types in SQL Server, and they are synonymous, meaning that their data types are exactly the same.
In Enterprise Manager, we can only choose timestamp as the column name, but in T-SQL (such as Query Analyzer), we can use either timestamp or rowversion.
So in T-SQL, which one should we use? We should use rowversion. Because the timestamp defined by Microsoft is different from the timestamp defined in ISO SQL-92, Microsoft also recommends using CREATE Table, CREATE Procedure, or DECLARE @Data Definition Language (DDL) statements like variable use rowversion instead of timestamp.
Microsoft may remove timestamp in future versions, but so far, including SQL Server 2008, timestamp has not been removed.
Additionally, to view more synonyms for SQL Server, please refer to Data Type Synonyms (Transact SQL) on MSDN.
Notes: .
1. Each database has a counter whose value increases when inserting or updating a table containing the timestamp column in the database This counter is the database timestamp;
2. A table can only have one timestamp column;
3. Note that deleting data operations cannot record timestamps, so your logic for deleting records should use a field to indicate that this row of records has been deleted;
4. This attribute makes the timestamp column unsuitable for use as a key, especially as a primary key;
If the column belongs to an index key, all updates to the data row will also result in index updates;
6. To return the current timestamp value of the database: SELECT@ @DBTS.
7. In DDL statements, please try to use rowversion instead of timestamp as there is no rowversion data type when designing tables in SSMS;
8. In the CREATE or Alter Table statements, it is not necessary to specify a column name for the timestamp data type. If a column name is not specified, the Microsoft SQL Server database engine will generate a timestamp column name; But the synonym rowversion does not exhibit such behavior When using rowversion, column names must be specified.
9. A non empty rowversion column is semantically equivalent to a binary (8) column A row version column that can be empty is semantically equivalent to a varbinary (8) column.
**************Transfer: https://www.cnblogs.com/smallidea/p/4627020.html.