rowversion (Transact-SQL)rowversion (Transact-SQL)

适用于: 是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: YesSQL Server YesAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

公开数据库中自动生成的唯一二进制数字的数据类型。Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion 通常用作给表行加版本戳的机制 。rowversion is generally used as a mechanism for version-stamping table rows. 存储大小为 8 个字节。The storage size is 8 bytes. rowversion 数据类型只是递增的数字,不保留日期或时间 。The rowversion data type is just an incrementing number and does not preserve a date or a time. 若要记录日期或时间,请使用 datetime2 数据类型 。To record a date or time, use a datetime2 data type.

备注Remarks

每个数据库都有一个计数器,当对数据库中包含 rowversion 列的表执行插入或更新操作时,该计数器值就会增加 。Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. 此计数器是数据库行版本。This counter is the database rowversion. 这可以跟踪数据库内的相对时间,而不是时钟相关联的实际时间。This tracks a relative time within a database, not an actual time that can be associated with a clock. 一个表只能有一个 rowversion 列 。A table can have only one rowversion column. 每次修改或插入包含 rowversion 列的行时,就会在 rowversion 列中插入经过增量的数据库 rowversion 值 。Every time that a row with a rowversion column is modified or inserted, the incremented database rowversion value is inserted in the rowversion column. 这一属性使 rowversion 列不适合作为键使用,尤其是不能作为主键使用 。This property makes a rowversion column a poor candidate for keys, especially primary keys. 对行的任何更新都会更改行版本值,从而更改键值。Any update made to the row changes the rowversion value and, therefore, changes the key value. 如果该列属于主键,那么旧的键值将无效,进而引用该旧值的外键也将不再有效。If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are 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 is in an index key, all updates to the data row also generate updates of the index. 使用任何更新语句都会让 rowversion 值递增,即使没有任何行值发生更改 。The rowversion value is incremented with any update statement, even if no row values are changed. (例如,如果某列的值为 5,且更新语句将该值设置为 5,即使没有发生任何更改,此操作也被视为更新,并且 rowversion 发生递增。 )(For example, if a column value is 5, and an update statement sets the value to 5, this action is considered an update even though there is no change, and the rowversion is incremented.)

timestamp 的数据类型为 rowversion 数据类型的同义词,并具有数据类型同义词的行为 。timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms. 在 DDL 语句中,应尽量使用 rowversion,而不是 timestamp 。In DDL statements, use rowversion instead of timestamp wherever possible. 有关详细信息,请参阅数据类型同义词 (Transact-SQL)For more information, see Data Type Synonyms (Transact-SQL).

Transact-SQLTransact-SQL timestamp 数据类型不同于在 ISO 标准中定义的 timestamp 数据类型 。The Transact-SQLTransact-SQL timestamp data type is different from the timestamp data type defined in the ISO standard.

备注

不推荐使用 timestamp 语法 。The timestamp syntax is deprecated. 此功能处于维护模式并且可能会在 Microsoft SQL Server 将来的版本中被删除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

在 CREATE TABLE 或 ALTER TABLE 语句中,不必为 timestamp 数据类型指定列名,例如: In a CREATE TABLE or ALTER TABLE statement, you do not have to specify a column name for the timestamp data type, for example:

CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, timestamp);  

如果不指定列名,则 SQL Server 数据库引擎SQL Server Database Engine 将生成 timestamp 列名;但 rowversion 同义词不具有这样的行为 。If you do not specify a column name, the SQL Server 数据库引擎SQL Server Database Engine generates the timestamp column name; however, the rowversion synonym does not follow this behavior. 在使用 rowversion 时,必须指定列名,例如: When you use rowversion, you must specify a column name, for example:

CREATE TABLE ExampleTable2 (PriKey int PRIMARY KEY, VerCol rowversion) ;  

备注

通过使用在其 SELECT 列表中包含了 rowversion 列的 SELECT INTO 语句,可以生成重复的 rowversion 值 。Duplicate rowversion values can be generated by using the SELECT INTO statement in which a rowversion column is in the SELECT list. 建议不要以这种方式使用 rowversion 。We do not recommend using rowversion in this manner.

不可为空的 rowversion 列在语义上等同于 binary(8) 列 。A nonnullable rowversion column is semantically equivalent to a binary(8) column. 可为空的 rowversion 列在语义上等同于varbinary(8) 列 。A nullable rowversion column is semantically equivalent to a varbinary(8) column.

可以使用某行的 rowversion 列轻松确定自上次读取该行后,是否对该行运行过更新语句 。You can use the rowversion column of a row to easily determine whether an update statement has been run against the row since the last time it was read. 如果对该行运行过更新语句,则会更新 rowversion 值。If an update statement is run against the row, the rowversion value is updated. 如果没有对该行运行过更新语句,则 rowversion 值将与以前读取该行时的 rowversion 值相同。If no update statements are run against the row, the rowversion value is the same as when it was previously read. 若要返回数据库的当前 rowversion 值,请使用 @@DBTSTo return the current rowversion value for a database, use @@DBTS.

当多个用户正在同时更新行时,可以在表中添加一个 rowversion 列以帮助维护数据库的完整性 。You can add a rowversion column to a table to help maintain the integrity of the database when multiple users are updating rows at the same time. 此外,您可能还想要在不重新查询表的情况下了解有多少行被更新以及哪些行被更新。You may also want to know how many rows and which rows were updated without re-querying the table.

例如,假定您创建了一个名为 MyTest 的表。For example, assume that you create a table named MyTest. 通过运行下面的 Transact-SQLTransact-SQL 语句在表中填充一些数据。You populate some data in the table by running the following Transact-SQLTransact-SQL statements.

CREATE TABLE MyTest (myKey int PRIMARY KEY  
    ,myValue int, RV rowversion);  
GO   
INSERT INTO MyTest (myKey, myValue) VALUES (1, 0);  
GO   
INSERT INTO MyTest (myKey, myValue) VALUES (2, 0);  
GO  

然后,可以使用以下示例 Transact-SQLTransact-SQL 语句在更新期间实现 MyTest 表的乐观并发控制。You can then use the following sample Transact-SQLTransact-SQL statements to implement optimistic concurrency control on the MyTest table during the update. 脚本使用 <myRv> 来表示上次读取该行时的 rowversion 值 。The script uses <myRv> to represent the rowversion value from the last time you read the row. 将该值替换为实际 rowversion 值 。Replace the value with the actual rowversion value. 一个实际的 rowversion 值的示例是 0x00000000000007D3An example of an actual rowversion value is 0x00000000000007D3.

DECLARE @t TABLE (myKey int);  
UPDATE MyTest  
SET myValue = 2  
    OUTPUT inserted.myKey INTO @t(myKey)   
WHERE myKey = 1   
    AND RV = <myRv>;  
IF (SELECT COUNT(*) FROM @t) = 0  
    BEGIN  
        RAISERROR ('error changing row with myKey = %d'  
            ,16 -- Severity.  
            ,1 -- State   
            ,1) -- myKey that was changed   
    END;  

还可以将示例 Transact-SQLTransact-SQL 语句放在事务中。You can also put the sample Transact-SQLTransact-SQL statements into a transaction. 通过在事务的作用域中查询 @t 变量,可以在不重新查询 myKey 表的情况下检索表的更新后的 MyTest 列。By querying the @t variable in the scope of the transaction, you can retrieve the updated myKey column of the table without requerying the MyTest table.

以下是使用 timestamp 语法的同一个示例 。The following is the same example using the timestamp syntax. <myTS> 替换为实际的 timestamp 。Replace <myTS> with an actual timestamp.

CREATE TABLE MyTest2 (myKey int PRIMARY KEY  
    ,myValue int, TS timestamp);  
GO   
INSERT INTO MyTest2 (myKey, myValue) VALUES (1, 0);  
GO   
INSERT INTO MyTest2 (myKey, myValue) VALUES (2, 0);  
GO  
DECLARE @t TABLE (myKey int);  
UPDATE MyTest2  
SET myValue = 2  
    OUTPUT inserted.myKey INTO @t(myKey)   
WHERE myKey = 1   
    AND TS = <myTS>;  
IF (SELECT COUNT(*) FROM @t) = 0  
    BEGIN  
        RAISERROR ('error changing row with myKey = %d'  
            ,16 -- Severity.  
            ,1 -- State   
            ,1) -- myKey that was changed   
    END;  

另请参阅See also

ALTER TABLE (Transact-SQL)ALTER TABLE (Transact-SQL)
CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)
CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL)
数据类型 (Transact-SQL)Data Types (Transact-SQL)
DECLARE @local_variable (Transact-SQL)DECLARE @local_variable (Transact-SQL)
DELETE (Transact-SQL)DELETE (Transact-SQL)
INSERT (Transact-SQL)INSERT (Transact-SQL)
MIN_ACTIVE_ROWVERSION (Transact-SQL)MIN_ACTIVE_ROWVERSION (Transact-SQL)
SET @local_variable (Transact-SQL)SET @local_variable (Transact-SQL)
UPDATE (Transact-SQL)UPDATE (Transact-SQL)