Helping a team with an implementation has it’s own unique challenges, and the biggest challenge is – they ask questions! I get a kick out of this challenge because it gives me and the teams around me a lot to learn. Currently while helping a team to implement optimistic concurrency, somebody asked me a question:

What is the next value of ROWVERSION at any given instant of time?

The question makes absolute sense. If we can determine the active value of an identity column and determine which value would be used next, there is no reason why we cannot determine the active value of a ROWVERSION column. After all, ROWVERSION is an auto-incrementing number.

The answer comes in the form of a non-deterministic function – MIN_ACTIVE_ROWVERSION. This function returns the lowest active ROWVERSION value in the current database. A ROWVERSION value is active if it is being used in a transaction that has not yet been committed.

Explanation

The definition from Books-On-Line is a little too heavy to digest in one go. So, here’s a break-down of the sentence.

A new ROWVERSION value is typically generated when an INSERT or UPDATE is performed on a table that contains a column using the ROWVERSION data-type. A transaction may be performing multiple INSERTs and UPDATEs – causing multiple changes to the ROWVERSION value. However, the lowest of these values (effectively, the value that was first used when the transaction was started) is what is returned by the function - MIN_ACTIVE_ROWVERSION().

For all practical purposes, this is same as the value of @@DBTS + 1.

Demonstration

Here’s a demonstration of the function – MIN_ACTIVE_ROWVERSION. This demo requires two active sessions to the SQL Server instance.

Let’s begin by creating a test table, and inserting some test data into it. We will use the MIN_ACTIVE_ROWVERSION() to determine the value of the ROWVERSION column that will be used by SQL Server.

USE tempdb
GO

--Safety Check
IF OBJECT_ID('SourceData') IS NOT NULL
BEGIN
    DROP TABLE SourceData
END
GO

--Create the demo (source) table
CREATE TABLE SourceData (RowId INT IDENTITY (1,1),
                         RowVal VARCHAR(20),
                         DataVersion ROWVERSION
                        )
GO

--Fetch the currently active value of ROWVERSION
SELECT MIN_ACTIVE_ROWVERSION() AS MinActiveRowVersion

--Now, insert some test data
INSERT INTO SourceData (RowVal) VALUES ('Windows'),
                                       ('SQL Server'),
                                       ('BizTalk Server'),
                                       ('Exchange Server')
GO

SELECT RowId, RowVal, DataVersion
FROM SourceData

--Fetch the currently active value of ROWVERSION
SELECT MIN_ACTIVE_ROWVERSION() AS MinActiveRowVersion

Now, switch to another session connected to the same SQL Server instance, and execute the following query:

-- In a separate window, begin a transaction, insert some test data, but do not commit
-- Session 02
USE tempdb
GO
BEGIN TRANSACTION MinRowVersion
    --Fetch the currently active value of ROWVERSION
    SELECT MIN_ACTIVE_ROWVERSION() AS MinActiveRowVersion
    
    INSERT INTO SourceData (RowVal) VALUES ('System Center'),
                                           ('Office')
    
    SELECT RowId, RowVal, DataVersion
    FROM SourceData
    
    --Fetch the currently active value of ROWVERSION
    SELECT MIN_ACTIVE_ROWVERSION() AS MinActiveRowVersion
    
--Session 02 - We are NOT performing a COMMIT on the transaction
--COMMIT TRANSACTION MinRowVersion

Although the transaction inserts two (2) records, the MIN_ACTIVE_ROWVERSION() returns the same value as seen at the end of the statements executed in session #01. This is because the transaction in session #02 is not yet committed.

Committing the transaction in session #02 should now reflect the updated value of ROWVERSION column value.

--Session 02
COMMIT TRANSACTION MinRowVersion

--Return back to Session 01
--Session 01
--Fetch the currently active value of ROWVERSION
SELECT MIN_ACTIVE_ROWVERSION() AS MinActiveRowVersion

--Cleanup
IF OBJECT_ID('SourceData') IS NOT NULL
BEGIN
    DROP TABLE SourceData
END
GO

References:

• MIN_ACTIVE_ROWVERSION(): http://technet.microsoft.com/en-us/library/bb839514(v=sql.110).aspx

Until we meet next time,

Be courteous. Drive responsibly.