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

This article introduces how to use timestamp data class in Sqlserver, the content is very detailed, interested friends can refer to, hope to be helpful to you.

In SQL Server, Books online says that the SQL Server timestamp data type has nothing to do with time and date. SQL Server timestamp is a binary number that indicates the relative order in which data changes occur in the database. The timestamp data type was originally implemented to support the SQL Server recovery algorithm. Each time the page is modified, it is marked once with the current @ @ DBTS value, and then @ @ DBTS plus 1. This is sufficient to help the recovery process determine the relative order of page changes, but the timestamp value has nothing to do with time. I use words to explain it is easy to understand: we know that the number type has an "identity" property, set "identification seed", "identification increment", each additional record, the value of this field will automatically increase based on the most recent identification value, so that we can know which records were added first and which records were added later, but we cannot know which records have been modified. For a field like timestamp, each time a record is added, it will be automatically added based on the most recent timestamp, and when a record is modified, it will also be automatically added based on the most recent timestamp, so we will know which records have been modified. The data type timestamp represents automatically generated binary numbers, ensuring that these numbers are unique in the database. Timestamp is generally used as a mechanism for stamping table rows. The storage size is 8 bytes. Note that the Transact-SQL timestamp data type is different from the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type. Future versions of Microsoft ®SQL Server ™may modify the behavior of the Transact-SQL timestamp data type to make it consistent with the behavior defined in the standard. By then, the current timestamp data type will be replaced with the rowversion data type. Microsoft ®SQL Server ™2000 introduces rowversion synonyms for timestamp data types. Use rowversion instead of timestamp as much as possible in DDL statements. Rowversion is restricted by the behavior of data type synonyms. For more information, see data type synonyms. In the CREATE TABLE or ALTER TABLE statement, it is not necessary to provide a column name for the timestamp data type: CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, timestamp) if no column name is provided, SQL Server generates the column name of the timestamp. Rowversion data type synonyms do not have this behavior. Column names must be provided when specifying rowversion. A table can have only one timestamp column. Each time a row containing a timestamp column is inserted or updated, the value in the timestamp column is updated. 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. A non-nullable timestamp column is semantically equivalent to a binary (8) column. A nullable timestamp column is semantically equivalent to a varbinary (8) column. Microsoft SQL Server stores values of the datetime data type internally with two 4-byte integers. The first 4 bytes stores the number of days before or after base date (that is, January 1, 1900). The base date is the system reference date. Datetime values earlier than January 1, 1753 are not allowed. Another 4-byte stores the time of day represented by milliseconds after midnight. The copy code is as follows: @ @ DBTS (SELECT @ @ DBTS) returns the value of the current timestamp data type for the current database. This timestamp value is guaranteed to be unique in the database. Syntax @ @ DBTS return type varbinary comment @ @ DBTS

Returns the last timestamp value used by the current database. When a row with a timestamp column is inserted or updated, a new timestamp value is generated.

On how to use the timestamp data class in Sqlserver to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.