A Quick SQL Server Puzzle About MIN_ACTIVE_ROWVERSION()

December 15, 2023

A Quick SQL Server Puzzle About MIN_ACTIVE_ROWVERSION()

Filed under: SQLServerPedia Syndication — Michael J. Swart @ 4:11 pm

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.

-- Comments (3)

3 Comments »

This chart shows common implicit conversions.
https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-ver16

I feel like maybe there should be a grey diamond in the intersection of the “int” type and the “binary” type.

It somehow feels wrong that these are the same:
declare @i1 int = 0x97fffffff; declare @i2 int = 0x17fffffff; select @i1, @i2 -- 2147483647, 2147483647
declare @i1 int = 0x97fffffff; declare @i2 int = 0x17fffffff; select @i1, @i2 -- 2147483647, 2147483647
Comment by Michael J. Swart — December 15, 2023 @ 4:46 pm
[…] Michael J. Swart does the hex math: […]

Pingback by Implicit Conversion of INT to BINARY – Curated SQL — December 18, 2023 @ 8:10 am
Great example of how implicit conversions will result in an unexpected result. It is the reason I try to either eliminate the datatype conversion or replace with an explicit conversion. It is not always easy as it is not always obvious, as in this example.

Here is another way to get the desired results.

SELECT CAST(0x017fffffff – CAST(1 AS bigint) AS rowversion);

Comment by Robert Eder — December 19, 2023 @ 10:19 pm