Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Rowversion Functions min_active_row_version vs. @@DBTS


By:   |   Last Updated: 2014-12-22   |   Comments (1)   |   Related Tips: 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 !!


Learn more about SQL Server tools