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.

score 5 · Answer 1 · answered Dec 04 '08 at 23:42

5

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.

answered Dec 04 '08 at 23:42

Ken Gentle

13,277
2
41
49

5

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? – Stephen Edmonds Mar 29 '10 at 08:51

score 2 · Answer 2 · answered Dec 04 '08 at 23:35

2

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

answered Dec 04 '08 at 23:35

JamesSugrue

14,891
10
61
93

score 0 · Answer 3 · answered Mar 12 '23 at 10:28

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.

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

3 Answers3