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

This article to share with you is about SQL Server database which timestamp data types, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after some harvest, not much to say, follow Xiaobian to see it.

SQL Server database timestamp data types

Popular explanation: We know that the number type has an "identification" attribute, set the "identification seed" and "identification increment", each time a record is added, the value of this field will automatically increase on the basis of the most recent identification value, so that we can know which records are added first and which records are added later, but we cannot know which records have been modified. The timestamp type field will automatically increase based on the latest timestamp for each record added, and will automatically increase based on the latest timestamp when a record is modified, so we know which records have been modified. The timestamp data type represents automatically generated binary numbers, ensuring that they are unique in the database. Timestamps are commonly used as a mechanism for versioning table rows. The storage size is 8 bytes.

Notes:

The Transact-SQL timestamp data type differs from the timestamp data type defined in the SQL-92 standard. SQL-92timestamp data type is equivalent to Transact-SQL datetime data type.

Future versions of Microsoft® SQL Server ™ may modify the behavior of the Transact-SQLtimestamp data type to align it with the behavior defined in the standard. At that point, the current timestamp data type will be replaced with the rowversion data type.

Microsoft® SQL Server ™2000 introduces the rowversion synonym for the timestamp data type. Use rowversion instead of timestamp whenever possible in DDL statements. Rowversion is governed by the behavior of data type synonyms. For more information, see Data Type Synonyms.

In CREATETABLE or ALTERTABLE statements, you do not have to provide column names for timestamp data types: CREATETABLEExampleTable(PriKeyintPRIMARYKEY,timestamp), SQL Server generates column names for timestamp if no column names are provided. Rowversion data type synonyms do not have this behavior. Column names must be provided when rowversion is specified.

SQL Server database timestamp data types

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 property makes timestamp columns unsuitable for use as keys, especially as primary keys. Any update to a row changes the timestamp value, which changes the key value. If the column is a primary key, the old key value is invalid, and the foreign key referencing the old value is no longer 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 an index update.

Non-nullable timestamp columns are semantically equivalent to binary(8) columns. A nullable timestamp column is semantically equivalent to a varbinary(8) column.

Microsoft SQL Server internally stores the value of the datetime data type as two 4-byte integers. The first 4 bytes store the number of days before or after the baseline (January 1, 1900). The base date is the system reference date. datetime values earlier than January 1, 1753 are not allowed. Another 4 bytes stores the time of day in milliseconds after midnight.

@@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.

The syntax is as follows: @@DBTS Return Type varbinary

Notes:

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

The above is what timestamp data types are in SQL Server database, Xiaobian believes that some knowledge points may be what we see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.