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.
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.
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
- MIN_ACTIVE_ROWVERSION(): http://technet.microsoft.com/en-us/library/bb839514(v=sql.110).aspx
Until we meet next time,
Be courteous. Drive responsibly.