Here is a very interesting question I received in an email just another day. Some questions just are so good that it makes me wonder how come I have not faced it first hand. Anyway here is the question –
“Pinal, I am migrating my database from MySQL to SQL Server and I have faced unique situation.
I have been using Unsigned 64-bit integer in MySQL but when I try to migrate that column to SQL Server, I am facing an issue as there is no datatype which I find appropriate for my column. It is now too late to change the datatype and I need immediate solution.
One chain of thought was to change the data type of the column from Unsigned 64-bit (BIGINT) to VARCHAR(n) but that will just change the data type for me such that I will face quite a lot of performance related issues in future. In SQL Server we also have the BIGINT data type but that is Signed 64-bit datatype. BIGINT datatype in SQL Server have range of -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807). However, my digit is much larger than this number.
Is there anyway, I can store my big 64-bit Unsigned Integer without loosing much of the performance of by converting it to VARCHAR.”
Very interesting question, for the sake of the argument, we can ask user that there should be no need of such a big number or if you are taking about identity column I really doubt that if your table will grow beyond this table. Here the real question which I found interesting was how to store 64-bit unsigned integer value in SQL Server without converting it to String data type. After thinking a bit, I found a fairly simple answer.
I can use NUMERIC data type.
I can use NUMERIC(20) datatype for 64-bit unsigned integer value, NUMERIC(10) datatype for 32-bit unsigned integer value and NUMERIC(5) datatype for 16-bit unsigned integer value. Numeric datatype supports 38 maximum of 38 precision.
Now here is another thing to keep in mind.
- Using NUMERIC datatype will indeed accept the 64-bit unsigned integer but in future if you try to enter negative value, it will also allow the same. Hence, you will need to put any additional constraint over column to only accept positive integer there.
- Here is another big concern, SQL Server will store the number as numeric and will treat that as a positive integer for all the practical purpose. You will have to write in your application logic to interpret that as a 64-bit Unsigned Integer. On another side if you are using unsigned integers in your application, there are good chance that you already have logic taking care of the same.
Reference: Pinal Dave https://blog.sqlauthority.com/ )
6 Comments. Leave new
When you announce winner for this contest posted on 11th June 2013
SQL SERVER – Puzzle #1 – Querying Pattern Ranges and Wild Cards
：）Thanks for your post. We meet this problem too. By the way. why not use binary(8) in sqlserver to store 64-bit int ?
:) thanks for your post. We meet this problems too. By the way, why not use binary(8) to store 64-bit int in sqlserver? Dose it has some defects？
@sarahcla you can’t do integer arithmetic with binary. e.g.
select cast(1 as binary(8)) + cast(1 as binary(8))
returns
0x00000000000000010000000000000001
not 2, as you might have thought
For unsigned 32-bit int instead use (64-bit) bigint, and for unsigned 16 bit int use a normal 32-bit int. bigint/int are smaller and faster than numeric(10)/numeric(5), and still handle all the values of the unsigned types.