你的位置:首页 > 数据库

[数据库]RowVersion数据类型

RowVersion数据类型是系统自动生成的,唯一的,二进制数字,RowVersion通常用作给Table的数据行加版本戳,存储大小为 8 个字节。RowVersion数据类型只是永恒递增的数字,不保留日期或时间,但是可以使用RowVersion来比较数据行更新时间的先后,如果@rv1<@rv2,那么表明@rv2的更新发生在@rv1之后。

每个数据库都只有一个自增的计数器(Counter),每次对拥有RowVersion 字段的Table执行Insert或Update命令,该计数器都会增加。一个Table最多有一个RowVersion 字段,只要对Table执行Insert或Update命令,该字段就会被更新为计数器(Counter)的最新值。

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. A table can have only one rowversion column. 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字段的特性:

  1. 由于每个数据库都只一个Counter,因此,RowVersion的值在数据库中是唯一的,所有拥有RowVersion的Table,其RowVersion字段的值都是不同的;
  2. RowVersion 只会递增,不会回滚;如果数据的更新(Insert或Update)的事务回滚,RowVersion字段不会回滚;
  3. TimeStamp 已过时,避免用于产品设计中,使用RowVersion代替;

一,查看当前数据库的RowVersion

1,全局变量@@DBTS用于返回当前数据库的RowVersion,@@DBTS 返回值的数据类型是varbinary(8)。

@@DBTS value is not rolled back when a transaction rolls back or when an INSERT or UPDATE query causes an error.

@@DBTS returns the value of the current rowversion data type for the database. The rowversion is guaranteed to be unique in the database. @@DBTS returns a varbinary which is the last-used rowversion value of the current database. A new rowversion value is generated when a row with a rowversion column is inserted or updated. Any INSERT, UPDATE and CREATE queries will internally increment the rowversion values.

2,非确定性函数 MIN_ACTIVE_ROWVERSION() 用于返回当前数据库的下一个RowVersion值,其值是@@DBTS+1。

MIN_ACTIVE_ROWVERSION is a non-deterministic function that returns the lowest active rowversion value in the current database. A new rowversion value is typically generated when an insert or update is performed on a table that contains a column of typerowversion. If there are no active values in the database, MIN_ACTIVE_ROWVERSION returns the same value as @@DBTS + 1.

select @@DBTS as dbts,MIN_ACTIVE_ROWVERSION() as min_active

二,示例

1,查看数据库当前的RowVersion

declare @rv rowversionset @rv=@@DBTSselect @rv as rv

2,创建含有RowVersion列的表,并插入数据

create table dbo.dt_rv(id int,rv rowversion not null)insert into dbo.dt_rv(id)values(1)

3,查看当前的RowVersion和表中的数据

select id,rv,@@dbts as dbts from dbo.dt_rv

4,更新表,查看RowVersion值的变化

update dbo.dt_rv set id=2 where id=1select id,rv,@@dbts as dbts from dbo.dt_rv

 5,测试事务rollback时,RowVersion值的变化

begin tran insert into dbo.dt_rv(id)values(1)rollback tran select id,rv,@@dbts as dbts from dbo.dt_rv

当事务回滚时,RowVersion的值不会回滚,RowVersion只会递增。

 

参考文档:

rowversion (Transact-SQL)