Apr 132012
 

Both @@DBTS and Min_active_RowVersion() are used to get the current Row Version in a database. Row version, is also called timestamp formerly, is an unsigned bigint data type of a column stored and presented as a binary(8). This data type is like a identity value of a table in which every table can only have one RowVersion column and the value of the row version is managed by SQL Server rather than uses, it’s read-only. When a new record is inserted into a table with RowVersion column, a row version will be assigned to the row. When update happens to the table, the row version of updated row will be increased. The values of the row version from tables within a database is always unique.

When you insert or update a table with RowVersion field, the row version of the row will get is unpredicatable, there is no functions like @@identity or scope_identity to determine what row version is in the row. But you can use @@DBTS or Min_Active_RowVersion to get current row versions.

First, let’s see how does RowVersion work

use tempdb
go
if object_id('test1') is not null
	drop table test1
if object_id('test2') is not null
	drop table test2
go
create table test1 (ID int primary key, rv RowVersion)
create table test2 (ID int primary key, rv RowVersion)
go
insert into test1 (ID)values(1)
insert into test2 (ID)values(1)
select * from test1
select * from test2
/*

(1 row(s) affected)

(1 row(s) affected)
ID          rv
----------- ------------------
1           0x00000000000007F9

(1 row(s) affected)

ID          rv
----------- ------------------
1           0x00000000000007FA

(1 row(s) affected)

*/
-- You can see that the row versions assigned to the rows in different tables are different

go
select @@DBTS, MIN_ACTIVE_ROWVERSION()
/*                 
------------------ ------------------
0x00000000000007FA 0x00000000000007FB

(1 row(s) affected)
*/

in this case, @@dbts tells the last used time stamp where the Min_Active_RowVersion tells what the next row version will be used. ( what’s the active row version). It does not very conclusive at the moment. Now let’s run following code in different sessions.

-- in session 1 run
begin transaction
update test1 set ID = ID
go
-- in session 2 run 
update test2 set ID = ID
-- then run following code in session 2
select * from test1 with (nolock)
select * from test2 
select @@DBTS, MIN_ACTIVE_ROWVERSION()

/*
ID          rv
----------- ------------------
1           0x00000000000007FB

(1 row(s) affected)

ID          rv
----------- ------------------
1           0x00000000000007FC

(1 row(s) affected)

------------------ ------------------
0x00000000000007FC 0x00000000000007FB

(1 row(s) affected)
*/

When update heppened to table test1 in session 1, the row version is updated to 0x07FB. Because the transaction is still active, Min_Active_RowVersion returns the rowversion value in the opened transaction. If the transaction is rolled back, the 0x07FB will be lost (or wasted). Now the table 2 is updated, the newer row version is assigned to the row being updated. The updated value is reflected to in @@DBTS rather than Min_Active_RowVersion.

The differences of those 2 are

  1. Min_Active_RowVersion returns the minimum active row version in current database. Active row versions refers to the row versions in transactions. If there isn’t row version in the transaction, the next rowversion will be used will becomes to the active row version
  2. @@DBTS returns the last row version has been used regardless the transaction status.

Row version field is used to audit changes on tables which is versy useful in data warehouse projects especially in the case when all the deletes turned to “soft delete”. Based on one of the values of @@DBTS or Max_Active_RowVersion, the ETL application can determine the changed rows since last process. After current process complete, it can save the current row version as last processed row version for next ETL processing cycle.

Will you use @@DBTS to determine current unprocessed row version or use Max_Active_RowVersion? Answer is the row versions less than Max_Active_RowVersion. The reason is that using Max_Active_Rowversion can guarantee the rows processed by ETL are already committed. If ETL uses @@DBTS to determine current row version, there is a chance that any active transaction holds the records until ETL process complete so that ETL could not see those records and would not process them. But when next ETL cycle comes, it ignores those rows since the row version of those rows are processed.

Share/Bookmark
Add Comment Register



 Leave a Reply

(required)

(required)

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="">

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers: