How do you store unsigned 64-bit integers in SQL Server?

Question

I work with an application which uses rather big numbers and I need to store data as an unsigned 64-bit integer. I prefer to just store it without worrying about bit manipulation or anything like that so different programs can use the data in different ways.

Buckskin

Answer 1

You can store the value in a NUMERIC type with a scale of 0, which will retain the integer semantics required. The NUMERIC type will allow negative numbers, although you could set up a constraint to require positive integers.

The maximum precision for NUMERIC is 38 decimal digits. 2**64 is somewhere around 18 or 19 decimal digits, so NUMERIC(19,0) would likely work just fine for this data.

Disinherit

Isn't 2 to the power of 64 '18,446,744,073,709,551,616' - or 20 digits? If so, would that mean you need a NUMERIC(20,0) to store it? – Uncrowned

Answer 2

AFAIK, You would have to create a custom type. Pointers here although that article is more for restricting negative numbers...

Ethiopia

Answer 3

Do not switch to the larger datatype just to accomodate the unsigned value. In C# and likewise for other languages cast the ulong (=unsigned 64 bit) value to the long (=signed 64 bit) and store that as a SQL-Server bigint (=signed 64 bit). On the way back read the bigint as a long and C#-cast it to ulong. All values survive, all bits are used, no excess bits are introduced.

Incognizant