converting sql server rowversion to long or ulong?

Question

What is the proper type for the rowversion (timestamp) data type?

I know it is 8 bytes but i cannot find a link in MSDN which tell if it is a signed or unsigned long.

which code should I use, does it even matter?

byte[] SqlTimeStamp;

long longConversion;
longConversion = BitConverter.ToInt64(SqlTimeStamp,0);
TimeStamp = BitConverter.GetBytes(longConversion);

ulong ulongConversion;
ulongConversion = BitConverter.ToUInt64(SqlTimeStamp,0);
TimeStamp = BitConverter.GetBytes(ulongConversion);

Solution 1

Short answer: it doesn't matter but I'd choose UInt64.

Details: semantically it's equivalent to binary(8) so, strictly speaking, it's neither UInt64 nor Int64 but just a chunk of bytes (and in that way it should be managed). That said I'd choose UInt64 because it's an incrementing number to hold row version then (from a logic point of view) 0xFFFFFFFFFFFFFFFF should be greater than 0 and it's not true for Int64 (because 64 bits set to 1 give -1 and it's less than 0).

Edit: note that, for reasons known only in the innest SQL Server designers circle, ROWVERSION is big-endian (while - obviously - bigint is not) then you first need to reverse bytes, see this answer for a nice implementation.

Solution 2

It does matter. You want your comparison to have the same result as SQL Server's comparison. SQL Server uses unsigned comparisons on binary types:

select case when 0x0FFFFFFFFFFFFFFF < 0xFFFFFFFFFFFFFFFF then 'unsigned' else 'signed' end

If you do the same thing with long which is signed, 0xFFFFFFFFFFFFFFFF represents -1. That means your comparison will be incorrect; it won't match with the same comparison done in SQL Server.

What you definitely want is to use ulong where 0xFFFFFFFFFFFFFFFF is ulong.MaxValue.

Endianness is also important

Additionally, as Mark pointed out, BitConverter.GetUInt64 is not converting properly. Mark is not completely right- BitConverter is either big-endian or little-endian depending on the system it's running on. You can see this for yourself. Also, even if BitConverter was always little-endian, Array.Reverse is less performant with a heap allocation and byte-by-byte copying. BitConverter is just not semantically or practically the right tool for the job.

This is what you want:

static ulong BigEndianToUInt64(byte[] bigEndianBinary)
{
    return ((ulong)bigEndianBinary[0] << 56) |
           ((ulong)bigEndianBinary[1] << 48) |
           ((ulong)bigEndianBinary[2] << 40) |
           ((ulong)bigEndianBinary[3] << 32) |
           ((ulong)bigEndianBinary[4] << 24) |
           ((ulong)bigEndianBinary[5] << 16) |
           ((ulong)bigEndianBinary[6] <<  8) |
                   bigEndianBinary[7];
}

The cleanest solution

Update: If you use .NET Core 2.1 or later (or .NET Standard 2.1), you can use BinaryPrimitives.ReadUInt64BigEndian which is a perfect fit.

On .NET Framework, here is the solution I use: Timestamp.cs. Basically once you cast to Timestamp, you can't go wrong.

Solution 3

Neither will work correctly for purposes of comparing timestamp/rowversion values, if you're running on an x86 family CPU, because of endian. The first byte of a timestamp is most significant, but not so for little endian integer types.

Call Array.Reverse(ts) before calling BitConverter.ToUInt64(ts), and for the other direction, after calling BitConverter.GetBytes(tsUInt64)

Solution 4

I use this (updated):

private UInt64 GetUInt64ForRowVersion(byte[] rowVersion)
{
    byte[] rr = (byte[])rowVersion.Clone();
    if (BitConverter.IsLittleEndian) { Array.Reverse(rr); }
    return BitConverter.ToUInt64(rr, 0);
}

View more solutions

Comments

[jnm2]

I'm a bit concerned that folks may get some subtly incorrect answers here on both the unsigned issue and the big-endian issue. Please see my answer.

[Mark McGinty]

They are not in order of significance on a little endian CPU. These values are incremented for each row that's updated, therefore if you persist MAX(rowversion) for a given set of rows after performing updates, you can determine which rows have been updated by testing rowversion > persistedrowversion. As long as you leave the values in SQL the comparison evaluates correctly, but if you need to compare them in app code, like say a linq query, then you have to fixup the byte order to match CPU endian.

[Mark McGinty]

Someone in another thread had a much more elegant solution, define a computed column in SQL that converts bin(8) to big_int. Likely somewhat faster and definitely less convoluted app code.

[Adriano Repetti]

Nice! Maybe not always applicable but pretty solution. If you still can find it please add link in your post

[jnm2]

BitConverter.ToUInt64 is little-endian or big-endian depending on the system. Better to do it in a portable way. Also more performant. See this answer.

[Adriano Repetti]

@jnm2 my +1, you're right, rowversion is stored as big-endian and your Timestamp class is pretty tidy for this purpose. I still wonder why rowversion is b.e. while bigint is obviously not...

[jnm2]

Here's one possible logical reason. Big-endian binary and strings can be treated the same and compared lexicographically. On top of that, if rowversion was bigint instead, it would be signed which is a problem for simple comparisons on rowversions. That makes binary (lexicographical comparison) the perfect choice, and lexicographical comparison is what makes things big-endian.

[jnm2]

Thanks, good conversation. I have a guess at the reason, already replied here.

[Adriano Repetti]

However (Unicode) strings UCS-2 (or UTF-16) are little-endian in SQL Server. I'd tend to agree with you it's something about ordering and comparison (for binary of different sizes), I saw also uniqueidentifier shares same weird byte ordering. True bigint is signed (even if honestly I can't imagine any DB that will reach 2^63 changes...)

[jnm2]

Yeah, there's more to the story. Also interesting is that the storage format of numeric is big endian while the storage format of the int types is little endian. Can verify by converting them to binary.

[Adriano Repetti]

I think you're to the point! I didn't check numeric but if it's big endian then it may be because of internal implementation as a byte array (can't say now which ordering is natural but I suppose big endian...). It may also be the reason rowversion is big endian, there may be some shared implementation there, after all numeric(20, 0) is a binary(8)...

[Mark McGinty]

Your function BigEndianToUInt64 is every bit as endian-dependent as what I suggested, and it's not qualified as such! :-) The point of my post was that the others were oblivious to the endian mismatch. Your statement that it is "not completely right" is not correct at all. Ok not most efficient, but does return correctly as qualified. What people copy to run on big endian CPUs from an explicit discussion of endian mismatch is not my concern. :-) Lastly, for absolute best-case efficiency/portability, do it in SQL (which is exactly what I do in practice.)

[jnm2]

It's big endian and not CPU-dependent which is precisely what is wanted here. It's purer. And it's also not possible to do everything server-side. For example, comparing loaded entities.

[Mo Chavoshi]

The most simplest and useful answer.

[Igor B]

DANGER: rowVersion getting mutated when using proposed solution, which tricks ChangeTracker to think that item got modified!

[Assaf S.]

@IgorB the array is clone. What is the danager?