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);

Sangria

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. – Plash

Answer 1

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.

Plash

I did qualify it with "if you're running on an x86 family CPU...", all of which are little endian. – Rennin

I would hate for someone to assume it was the right idiom though. It's less efficient memory and CPU and less elegant. Plus someone will copy it on a big endian CPU one of these days- better just not to couple it to the CPU in the first place. – Plash

@Plash 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... – Damsel

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

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.) – Rennin

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. – Plash

the function "BigEndianToUInt64" works, and how to convert ulong back to rowversion ? – Emissary

@GoldliZh gist.github.com/jnm2/… – Plash

BigEndianToUInt64 is absolutely CPU dependent, it reverses byte order, it assumes the caller is running on a little endian system. Its input is already big endian. On a big endian system this function returns an incorrect result. If you need to evaluate in linq, convert on SQL server and return the converted values to the client, that is the cleanest, most accurate, most performant way. – Rennin

@MarkMcGinty I want to understand what you're saying, but if I mentally run this code on a big-endian system, I think it's still correct. The byte array's [0] element is the most significant part of the rowversion coming from ADO.NET, and this code left-shifts it as a ulong by 56 bits which makes it the most significant part of the ulong. All assuming a big-endian system. (Left shift on big-endian system means "towards MSB" not "towards the left." Can you point out where exactly I'm making a mistake? If I can understand, I'll make sure to update. – Plash

Answer 2

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.

Damsel

It actually does matter! If you don't get this right, the result of your comparison will not match SQL Server's comparison. See this answer. – Plash

Sorry, that is not true. SQL Server allows < and > on binary. Your client-side logic should match SQL Server's logic exactly or it is buggy. Also, plenty of people filter on timestamp (binary(8)). It's an established practice. I just came from answering a different question about it and I'm in the middle of using it for the third time in an enterprise project. – Plash

Also, even if the fact that SQL Server has well-defined unsigned big-endian binary comparison operators doesn't convince you, what about the fact that SQL Server increments a binary(8) as though it was an unsigned big-endian int64? That's something you definitely need explicit knowledge of any time you deal with a timestamp column. – Plash

You saying that reminds me of a guy who said that numeric IDs in C# should disallow any operator except == and !=. I appreciated what he was after. Only problem is, < and > are still very useful for IDs if you ever need to get every unique pair of IDs. And using those comparisons in that case is absolutely legitimate. It's the purest thing to do. (Wish I could find the link to the blog post.) – Plash

No, I'm not confused between binary and writing ints in 0x representation. I don't know what else to say. MSDN: "A nonnullable rowversion column is semantically equivalent to a binary(8) column". Examine the binary(8) and you will see that it is incremented in big-endian fashion. When you filter on it to see if there are altered rows in a table, you compare it with <. Examine any binary comparison and you will see big-endian comparison. It's not me saying that, it's the authors of SQL Server. – Plash

@Plash wait. I checked with dbcc page and I just learned something new: you're right, rowversion is stored as big endian! Digging into this, I can't imagine any logic reason they did it like this...pretty interesting – Damsel

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. – Plash

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...) – Damsel

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. – Plash

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)... – Damsel

Answer 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)

Rennin

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. – Rennin

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. – Rennin

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

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. – Plash

Answer 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);
}

Colorblind

The most simplest and useful answer. – Zenithal

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

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