Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

MIN_ACTIVE_ROWVERSION (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Returns the lowest active rowversion value in the current database. A rowversion value is active if it is used in a transaction that has not yet been committed. For more information, see rowversion (Transact-SQL).

Note Note

The rowversion data type is also known as timestamp.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

MIN_ACTIVE_ROWVERSION

Returns a binary(8) value.

MIN_ACTIVE_ROWVERSION is a non-deterministic function that returns the lowest active rowversion value in the current database. A new rowversion value is typically generated when an insert or update is performed on a table that contains a column of type rowversion. If there are no active values in the database, MIN_ACTIVE_ROWVERSION returns the same value as @@DBTS + 1.

MIN_ACTIVE_ROWVERSION is useful for scenarios such as data synchronization that use rowversion values to group sets of changes together. If an application uses @@DBTS rather than MIN_ACTIVE_ROWVERSION, it is possible to miss changes that are active when synchronization occurs.

The MIN_ACTIVE_ROWVERSION function is not affected by changes in the transaction isolation levels.

The following example returns rowversion values by using MIN_ACTIVE_ROWVERSION and @@DBTS. Notice that the values differ when there are no active transactions in the database.

-- Create a table that has a ROWVERSION column in it.
CREATE TABLE RowVersionTestTable (rv ROWVERSION)
GO

-- Print the current values for the database.
PRINT ''
PRINT 'DBTS'
PRINT @@DBTS
PRINT 'MIN_ACTIVE_ROWVERSION'
PRINT MIN_ACTIVE_ROWVERSION() 
GO
---------------- Results ----------------
--DBTS
--0x00000000000007E2
--MIN_ACTIVE_ROWVERSION
--0x00000000000007E3

-- Insert a row.
INSERT INTO RowVersionTestTable VALUES (DEFAULT)
SELECT * FROM RowVersionTestTable
GO
---------------- Results ----------------
--rv
--0x00000000000007E3

-- Print the current values for the database.
PRINT ''
PRINT 'DBTS'
PRINT @@DBTS
PRINT 'MIN_ACTIVE_ROWVERSION'
PRINT MIN_ACTIVE_ROWVERSION()
GO
---------------- Results ----------------
--DBTS
--0x00000000000007E3
--MIN_ACTIVE_ROWVERSION
--0x00000000000007E4

-- Insert a new row inside a transaction but do not commit.
BEGIN TRAN
INSERT INTO RowVersionTestTable VALUES (DEFAULT)
SELECT * FROM RowVersionTestTable
GO
---------------- Results ----------------
--rv
--0x00000000000007E3
--0x00000000000007E4

-- Print the current values for the database.
PRINT ''
PRINT 'DBTS'
PRINT @@DBTS
PRINT 'MIN_ACTIVE_ROWVERSION'
PRINT MIN_ACTIVE_ROWVERSION() 
GO
---------------- Results ----------------
--DBTS
--0x00000000000007E4
--MIN_ACTIVE_ROWVERSION
--0x00000000000007E4

-- Commit the transaction.
COMMIT
GO

-- Print the current values for the database.
PRINT ''
PRINT 'DBTS'
PRINT @@DBTS
PRINT 'MIN_ACTIVE_ROWVERSION'
PRINT MIN_ACTIVE_ROWVERSION()
GO
---------------- Results ----------------
--DBTS
--0x00000000000007E4
--MIN_ACTIVE_ROWVERSION
--0x00000000000007E5

Community Additions

ADD
Show:
© 2015 Microsoft