Data type for unsigned integer?

_{Saturday, December 12, 2015 3:05 AM}

Hi,

I want to add an unsigned integer field(4 bytes) to my table. The range of the field will be 0 to 4294967295.

If I defines the field as int, as follows:

CREATE TABLE MyTable (MyField int);

Then since the range of int data type is from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647), then if I execute the following statement:

INSERT INTO MyTable VALUES (4294967295);

 

there will be an overflow error.

 

How to solve this problem? Should I define the field as bigint(8 bytes) so that it is large enough to hold the value of 4294967295? But using a bigint seems a bit waste since a lot of bits are not used at all.

 

Thanks

_{Sunday, December 13, 2015 10:54 PM ✅Answered}

In that case, I would stored it as binary(4), I think.

_{Saturday, December 12, 2015 8:23 AM}

Microsoft's Jim Hogg has responded to this issue with the following:

Yes, the question of supporting unsigned integers has popped up up several times over the years.

There are pros and cons. On the pro side, it seems like a good way to avoid some errors - having to check a (signed) int has value > 0. And I would also venture that many uses of int in fact relate to counts that should never be negative anyway. On the question of doubling max row count? - true, but I would say this is less compelling.

On the cons side ... mixing signed/unsigned types in C or C++ seems like it should be simple enough. It's not. It opens a small tarpit of hard-to-find mistakes - most due to the complex rules for implicit promotions/widenings. SQL, alas, already has an even more complex set of implicits casting rules. Adding unsigned ints, I fear, would confuse us all even more.

I'll keep this suggestion on the books. But, among all the features we could/should be adding, this one, with respect, is not near the top of that list.

Source:

UNSIGNED INTEGER Data Type

Píldoras .NET
Artículos, tutoriales y ejemplos de código .NET

Píldoras JS
Artículos, tutoriales y ejemplos de código JavaScript, HTML5, CSS3, ...

_{Saturday, December 12, 2015 10:59 AM}

The other two alternatives I can think of are:

binary(4) - you will need to cast to integer every time.

A user-defined type in the CLR.

_{Saturday, December 12, 2015 3:55 PM}

How to solve this problem? Should I define the field as bigint(8 bytes) so that it is large enough to hold the value of 4294967295?

But using a bigint seems a bit waste since a lot of bits are not used at all.

I would use bigint.  If you are using Enterprise Edition, you can use row or page compression so that fixed length columns use only the required space.

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

_{Saturday, December 12, 2015 4:37 PM}

Hi,

There's no unsigned int in SQL Server, but you can use a numeric(10,0) with a constraint "less than 4294967296"

numeric(10,0) takes 5 bytes, less than bigint 8 bytes though not as efficient as a native unsigned int would be

declare @x as numeric(10,0) = 4294967295
select @x as X_Value
select datalength(@x) as X_DataLength

_{Saturday, December 12, 2015 5:18 PM}

One of the major goals of SQL was to detach the language from its physical representation and make it as abstract as possible. Your mindset seems to be back in assembly language and C programming.

You are even use the wrong terms; "field" in SQL means part of a temporal value (year, month, day, hour, minute, second). In low-level assembly language and filesystems, the field is part of a physical record, the way you are using it here. But a row is not a record, and a column is not the field.

Here is a weird kludge using decimal (S,P) data types. Why do you need this particular data type, instead of an exact numeric data type? The concept of exact numeric data type is hardware independent.

CREATE TABLE ZZZ
(foobar DECIMAL (10,0) NOT NULL CHECK (foobar BETWEEN  0 AND  4294967295),
 ..);

If this were a woodworking forum, and someone asked "the best kinds of rocks to drive screws", then you might think he does not understand some fundamental concepts.

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

_{Saturday, December 12, 2015 6:49 PM | 1 vote}

Hi Celko,

1. I agree that, in an ideal world, we should have precise and efficient representations for every type of data we may ever need. Problem is that Microsoft SQL Server doesn't offer one for 4294697295, you have to go with either bloated representations like bigint or clumsy ones like numeric(10,0).

2. On the other hand, databases are always used in conjunction with applications (programming language) where concepts like unsigned int, char[5], nvarchar(40) or bit do make a lot sense.

_{Saturday, December 12, 2015 11:02 PM}

Hi Celko,

1. I agree that, in an ideal world, we should have precise and efficient representations for every type of data we may ever need. Problem is that Microsoft SQL Server doesn't offer one for 4294697295, you have to go with either bloated representations like bigint or clumsy ones like numeric(10,0).

2. On the other hand, databases are always used in conjunction with applications (programming language) where concepts like unsigned int, char[5], nvarchar(40) or bit do make a lot sense.

Hi,

Thank you. I am using ADO/Visual C++ to access SQL Server, so is it possible to make the cast in C++ level, as follows:

In SQL Server, create the field type as int.

When insert an unsigned int, I will convert it into an int via (int)Value in C++, then insert the value(may be negative) to SQL Server.

After retrieving the value from C++, I will always convert it(may be negative) back into an unsigned int via (unsigned int)Value in C++.

Is that OK?

Thanks

_{Sunday, December 13, 2015 9:34 AM}

Hi,

 I will suggest below Syntax will creating a table,

CREATE TABLE MyTable (MyField int check(MyField>=0 and MyField<=4294967295));

_{Sunday, December 13, 2015 10:46 AM}

When insert an unsigned int, I will convert it into an int via (int)Value in C++, then insert the value(may be negative) to SQL Server.

After retrieving the value from C++, I will always convert it(may be negative) back into an unsigned int via (unsigned int)Value in C++.

I guess that while clumsy it works.

But a more relevant question might be what this number represents. Is this an actual number, e.g. "today we have 3 147 551 196 widgets in store", or "this is account 4 012 122 366". Or more precisely, why this particular range? That the lower bound is 0 is not so questionable, but what business rule says that you will never exceed 0xFFFFFFFF?

_{Sunday, December 13, 2015 11:09 AM}

Hi,

 

I want to add an unsigned integer field(4 bytes) to my table. The range of the field will be 0 to 4294967295.

 

If I defines the field as int, as follows:

 

CREATE TABLE MyTable (MyField int);

 

Then since the range of int data type is from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647), then if I execute the following statement:

 

INSERT INTO MyTable VALUES (4294967295);

 

there will be an overflow error.

 

How to solve this problem? Should I define the field as bigint(8 bytes) so that it is large enough to hold the value of 4294967295? But using a bigint seems a bit waste since a lot of bits are not used at all.

 

Thanks

Try

Decimal(16,0)

instead.

Many Thanks & Best Regards, Hua Min

_{Sunday, December 13, 2015 10:41 PM}

When insert an unsigned int, I will convert it into an int via (int)Value in C++, then insert the value(may be negative) to SQL Server.

After retrieving the value from C++, I will always convert it(may be negative) back into an unsigned int via (unsigned int)Value in C++.

I guess that while clumsy it works.

But a more relevant question might be what this number represents. Is this an actual number, e.g. "today we have 3 147 551 196 widgets in store", or "this is account 4 012 122 366". Or more precisely, why this particular range? That the lower bound is 0 is not so questionable, but what business rule says that you will never exceed 0xFFFFFFFF?

Hi,

Thank you. I store the 32bit address in a temporary SQL Server table, since the 32bit address will always be less than the maximum value of 32bit unsigned integer, so the upper limit will be 0xFFFFFFFF.

_{Monday, December 14, 2015 1:24 PM}

Hi,

Unfortunately, I can't tell you if your strategy will work or not, it's been 20 years since the last time I touched a piece of C++ code.