MIN_ACTIVE_ROWVERSION (Transact-SQL)

New: 15 September 2007

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 timestamp (Transact-SQL).

Transact-SQL Syntax Conventions

Syntax

MIN_ACTIVE_ROWVERSION

Return Types

Returns a binary(8) value.

Remarks

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.

Examples

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

See Also

Reference

@@DBTS (Transact-SQL)
timestamp (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance