SQL Server Rowversion Functions min_active_row_version vs. @@DBTS

Problem

For data synchronization projects, using rowversion is a very effective method for detecting changes. In order to work with the rowversion properly, we would need to either use @@DBTS or the function min_active_rowversion. Understanding the differences between these values will help in building synchronization processes correctly.

Solution

The min_active_row_version function was added starting with SQL Server 2005. This function was created to handle some synchronization problems. It behaves very similar to @@DBTS.

Rowversion, also referred to as timestamp, is a datatype that was added in SQL Server 2005. It is a hexadecimal value, essentially a binary(8) that is used to represent the version of the row. When a record is created or updated, the rowversion for that record is incremented.

@@DBTS will return the current maximum rowversion in the database. Min_active_row_version will return the rowversion of oldest uncommitted transaction if there are any. Otherwise it will return essentially @@DBTS +1.

What we’ll see is that the @@DBTS value increments every time we do an insert, even though it does not commit. The min_active_row_version does not actually change until we start committing transactions.

In another test, we can find out some further interesting behavior. In fact, min_active_row_version will not increment unless we commit or rollback the oldest active transaction.

Test 1 for Rowversion Using @@DBTS and min_active_rowversion

Test Plan:

1. Create table
2. Review @@DBTS and min_active_rowversion
3. Insert with session 1.
4. Review @@DBTS and min_active_rowversion
5. Insert with session 2.
6. Review @@DBTS and min_active_rowversion
7. Commit session 1.
8. Review @@DBTS and min_active_rowversion
9. Commit session 2.
10. Review @@DBTS and min_active_rowversion

We will use the following query to review @@DBTS and min_active_rowversion:

select min_active_rowversion() as MinActiveRowVersion,@@dbts [@@DBTS]

First, let’s create a test table to work with.

create table dbo.testtable ( 
 value1 int,
 value2 varchar(10),
 VersionStamp rowversion
)

Every step of the way, we’ll check the values for @@DBTS and min_active_rowversion. The actual values will differ for every system, but the behavior will be the same.

Starting off with the process, I had the following values for @@DBTS and min_active_rowversion:

Open a new query with SQL Server Management Studio and execute the following:

begin tran
insert dbo.testtable (value1,value2) values (1,'one');

Notice that there is not a “commit tran”. We do this because this is actually where we start to see a lot of the differences between @@DBTS and min_active_rowversion.

Now I received the same value for both @@DBTS and min_active_rowversion. Notice that @@DBTS incremented, but min_active_rowversion did not.

Now, go to the first query session and run the following:

commit tran

Now after checking @@DBTS and min_active_rowversion, we see min_active_rowversion updated, giving us the same value for both.

Go to the second query session and run a “commit tran” command.

Now we see the min_active_rowversion increase again.

We learn something interesting about this. If min_active_rowversion is equal to @@DBTS +1, then there are no uncommitted transactions currently.

Test 2 for Rowversion Using @@DBTS and min_active_rowversion

Now, let’s try a different test. In the previous test we committed the transactions in order. Because of this, we saw min_active_rowversion increment with each commit. For this test, we’ll do two more inserts, but we’ll commit them in reverse order.

Test Plan:

1. Review @@DBTS and min_active_rowversion
2. Insert with session 3.
3. Review @@DBTS and min_active_rowversion
4. Insert with session 4.
5. Review @@DBTS and min_active_rowversion
6. Commit session 4.
7. Review @@DBTS and min_active_rowversion
8. Commit session 3.
9. Review @@DBTS and min_active_rowversion

Open a new query with SQL Server Management Studio and execute the following:

begin tran
insert dbo.testtable (value1,value2) values (3,'three');

Again, you’ll see there is no “commit”.

After reviewing the values for @@DBTS and min_active_rowversion, we’ll see they are the same:

Open a new query with SQL Server Management Studio and execute the following:

begin tran
insert dbo.testtable (value1,value2) values (4,'four');

Once again, there’s no “commit”.

Notice @@DBTS is larger than min_active_rowversion:

Now, let’s commit transaction four.

When we review, we see that the values have not changed.

Why is this the case? Because min_active_rowversion represents the oldest uncommitted transaction, which would be transaction three.

Now, let’s commit transaction three.

After the commit, we see that things are back to normal. Min_active_rowversion is @@DBTS+1 again.

Which one: @@DBTS or min_active_rowversion

Which should we use for data synchronization and ETL processes then?

The advantage of min_active_rowversion is that it allows us to differentiate between committed and uncommitted transactions. When using synch or ETL processes that use transaction isolation level read committed, then min_active_rowversion looks like it would be the better choice. We would only be looking at committed data if we always extracted data with rowversion values less than min_active_rowversion.

@@DBTS is an older technology and doesn’t have as many applicable cases. However, @@DBTS does allow us to see when transactions are in-progress. In some rare cases, there may be a need to look specifically at read uncommitted data, and that would make @@DBTS the more useful option.

Next Steps

• Use Rowversion to reduce load times with SSIS.
• Read about Locking Concurrency with Rowversion.