SQL Server Rowversion Functions min_active_row_version vs. @@DBTS


By:   |   Updated: 2014-12-22   |   Comments (2)   |   Related: More > T-SQL


Development Best Practices for SQL Server

Free MSSQLTips Webinar: Development Best Practices for SQL Server

Attend this webinar to learn about development best practices for SQL Server. Andy Warren will share his many years of experience to give some pointers on what has worked best for him and how you can utilize some of this knowledge.


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:

Rowversion Output

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.

Rowversion Output

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.

Rowversion Output

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

Now we see the min_active_rowversion increase again.

Rowversion Output

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:

Rowversion Output

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:

Rowversion Output

Now, let's commit transaction four.

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

Rowversion Output

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.

Rowversion Output

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


Last Updated: 2014-12-22


get scripts

next tip button



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

View all my tips



Comments For This Article




Tuesday, August 25, 2020 - 7:28:57 PM - Adriaan Back To Top (86360)
Thanks Robert
One thing I only noticed today that is causing some friction for us.
We have availability groups setup between primary and 3 read-only replicas.
Unfortunately, the value of MIN_ACTIVE_ROWVERSION() is much LARGER on the read-only replicas than on the Primary. This is very unfortunate as we are using these replicas for data synchronization - but because it returns larger values than on primary, we end up missing records (because we've synced up to the larger value and hence don't see values between MIN_ACTIVE_ROWVERSION on primary and secondary). It seems like the active row version is advanced in "chunks".
As an example current value on our primary is 9578903 (and this updates very frequently on busy database, as one would expect)
But, all the read-only replicas return 9592001

Sunday, January 04, 2015 - 11:30:10 AM - Rick Willemain Back To Top (35818)

Excellent topical review !  Thank you !!



download





Recommended Reading

SQL Server Cursor Example

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

Find MAX value from multiple columns in a SQL Server table

SQL Server Loop through Table Rows without Cursor














get free sql tips
agree to terms