mssqltips logo

SQL Server Rowversion Functions min_active_row_version vs. @@DBTS

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

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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

Excellent topical review !  Thank you !!



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools