MIN_ACTIVE_ROWVERSION() is a system function that returns the lowest active rowversion value in the current database. Its use then is very similar to @@DBTS.
In fact, the docs for MIN_ACTIVE_ROWVERSION() (currently) say:
If there are no active values in the database,
MIN_ACTIVE_ROWVERSION()returns the same value as@@DBTS + 1.
Does it though? You may be tempted then to replace some of your @@DBTS expressions with expressions like this:
Broken example
/* This is not always equivalent to @@DBTS */ SELECT CAST(MIN_ACTIVE_ROWVERSION() - 1 AS rowversion); |
Try to figure out why this is broken before reading further.
The problem
The problem occurs when the values get large. In fact you can reproduce this behavior with:
/* This may also give unexpected results */ SELECT CAST(0x017fffffff - 1 AS rowversion); /* 0x000000007FFFFFFE? Where did that leading one go? */ |
But why?
The issue is in the expression MIN_ACTIVE_ROWVERSION() - 1. SQL Server will try to subtract an int from a binary(8). To do that, it converts only the last four bytes of the binary(8) value to an int. It does that happily without any errors or warnings, even if the first four bytes are not zeros.
A fix
When we subtract, we want bigint arithmetic:
/* This gives the value we want*/ SELECT CAST(CAST(MIN_ACTIVE_ROWVERSION() AS BIGINT) - 1 AS rowversion); |
There may be more elegant solutions.
