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

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](http://stackoverflow.com/a/38149945/521757). — jnm2, Jul 01 '16 at 16:21

jnm2 · Answer 1 · 2021-05-08T15:25:23.050

24

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.

edited May 08 '21 at 15:25

answered Jul 01 '16 at 16:18

jnm2

7,157
5
57
92

I did qualify it with "if you're running on an x86 family CPU...", all of which are little endian. – Mark McGinty Jul 03 '16 at 16:40
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. – jnm2 Jul 03 '16 at 19:34
1

@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... – Adriano Repetti Jul 04 '16 at 07:48
Thanks, good conversation. I have a guess at the reason, already replied [here](https://stackoverflow.com/questions/21146023/converting-sql-server-rowversion-to-long-or-ulong/21146120?noredirect=1#comment63798046_21146120). – jnm2 Jul 04 '16 at 13:49
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.) – Mark McGinty Jul 04 '16 at 20:41
1

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. – jnm2 Jul 04 '16 at 20:45
the function "BigEndianToUInt64" works, and how to convert ulong back to rowversion ? – Goldli Zh May 16 '21 at 13:19
@GoldliZh https://gist.github.com/jnm2/929d194c87df8ad0438f6cab0139a0a6#file-rowversion-cs-L39-L51 – jnm2 May 17 '21 at 13:08

score 9 · Answer 2 · answered Apr 30 '14 at 22:58

9

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)

answered Apr 30 '14 at 22:58

Mark McGinty

736
6
13

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 Jun 10 '15 at 23:07
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. – Mark McGinty Jun 12 '15 at 18:32
Nice! Maybe not always applicable but pretty solution. If you still can find it please add link in your post – Adriano Repetti Jun 12 '15 at 19:17
`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](http://stackoverflow.com/a/38149945/521757). – jnm2 Jul 01 '16 at 16:18

score 8 · Accepted Answer · edited May 23 '17 at 11:54

8

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.

edited May 23 '17 at 11:54

Community

1
1

answered Jan 15 '14 at 19:08

Adriano Repetti

60,141
17
127
190

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](http://stackoverflow.com/a/38149945/521757). – jnm2 Jul 01 '16 at 16:20
Sorry, that is not true. SQL Server allows `` 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. – jnm2 Jul 01 '16 at 17:18
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. – jnm2 Jul 01 '16 at 17:26
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, `` 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.) – jnm2 Jul 01 '16 at 17:28
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](https://msdn.microsoft.com/en-us/library/ms182776.aspx)". 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 ` – jnm2 Jul 03 '16 at 21:12
@jnm2 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 – Adriano Repetti Jul 04 '16 at 07:33
1

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 Jul 04 '16 at 13:44
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...) – Adriano Repetti Jul 04 '16 at 14:06
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. – jnm2 Jul 04 '16 at 14:59
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)... – Adriano Repetti Jul 04 '16 at 15:39

A.J.Bauer · Answer 4 · 2019-06-29T04:48:50.777

1

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

edited Jun 29 '19 at 04:48

answered Jan 11 '18 at 05:59

A.J.Bauer

2,383
22
29

The most simplest and useful answer. – Mohammad Chavoshi Jul 17 '18 at 11:01
DANGER: rowVersion getting mutated when using proposed solution, which tricks ChangeTracker to think that item got modified! – Igor B Jun 27 '19 at 18:36
@IgorB the array is clone. What is the danager? – Assaf S. Jan 17 '21 at 14:49

converting sql server rowversion to long or ulong?

4 Answers4

Endianness is also important

The cleanest solution

Linked