Working with an unsigned BIGINT in SQL Server

I'm trying to store and retrieve an unsigned int64 using Microsoft SQL Server (and ultimately using ADO.NET EF), but I'm having some trouble. Since you cannot specify an integer column as unsigned in SQL Server, I tried using binary(8), however that's not working like I expected. The following queries result in an unexpected value (0x03E8000000000000 and 281474976710656000):

UPDATE table SET uint64 = 0x3E8
SELECT uint64, cast(uint64 as bigint) FROM table

If I run the following,开发者_JAVA百科 however, it seems to work OK (returning 0x00000000000003E8, 1000):

UPDATE table SET uint64 = 1000
SELECT uint64, cast(uint64 as bigint) FROM table

Unfortunately though, the SQL created by the Entity Framework seems to run the 1st update statement (I use BitConverter.ToBytes() when converting my ulong variable).

Is this my best bet if I want to store an unsigned 64-bit integer in SQL Server? Is there a better option than using BitConverter (other than writing my own to sort of LPAD the array with the appropriate number of zeros)?

You could use decimal (p, 0) and a check constraint to ensure >= 0.

Choose p to be big enough for your positive integers

I would at least look at varbinary fields, since you will have lots of these kinds of issues with unsigned/signed into a BIGINT.