SQL Server Rowversion Functions min_active_row_version vs. @@DBTS

By: Robert Biddle   |   Comments (2)   |   Related: > TSQL

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.

About the author

Robert Biddle is a Data Architect with over 10 years of experience working with databases. He specializes in data warehousing and SSIS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips