MIN_ACTIVE_ROWVERSION (Transact-SQL)
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
The rowversion data type is also known as timestamp.
.gif "Topic link icon")
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