integer datatype confusion, signed vs unsigned

Hi Group
Transact SQL defines that int is:
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 -
1 (2,147,483,647). Storage size is 4 bytes.
This implies that only SIGNED integer values are possible with
SQL-Server. I'm aware that from a data conversion point of view this
is no problem in that a singed integer can be interpreted as unsigned
or signed.
Then, there is a (c) datatype definition SQLUINTEGER as well as
SQLINTEGER, so unsigned integers seem to be suported conversion wise.
However, when it comes to sorting or using select, things are IMHO
different in that -1 is smaller than '0' if interpreted as signed, but
obviousely the biggest possible value interpreted as unsigned etc. I
therefore somehow miss the possibility to declare an integer collumn
to be "unsigned" so as sorting etc. is made the right way.
The same problem obviousely exists with small integers except that
their range is limitted to what can be expressed with 16 bits.
Could someone sheed some light on this?
TIA
MarkusThe INT datatype is signed, as are BIGINT and SMALLINT. TINYINT is
the only one not signed. This is can not be changed.
If negative numbers are not valid for a column, enforce that with a
CHECK constraint:
CHECK (IntCol >= 0)
Roy Harvey
Beacon Falls, CT
On Wed, 19 Apr 2006 11:26:57 +0200, Markus Zingg <m.zingg@.nct.ch>
wrote:

>Hi Group
>Transact SQL defines that int is:
>Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 -
>1 (2,147,483,647). Storage size is 4 bytes.
>This implies that only SIGNED integer values are possible with
>SQL-Server. I'm aware that from a data conversion point of view this
>is no problem in that a singed integer can be interpreted as unsigned
>or signed.
>Then, there is a (c) datatype definition SQLUINTEGER as well as
>SQLINTEGER, so unsigned integers seem to be suported conversion wise.
>However, when it comes to sorting or using select, things are IMHO
>different in that -1 is smaller than '0' if interpreted as signed, but
>obviousely the biggest possible value interpreted as unsigned etc. I
>therefore somehow miss the possibility to declare an integer collumn
>to be "unsigned" so as sorting etc. is made the right way.
>The same problem obviousely exists with small integers except that
>their range is limitted to what can be expressed with 16 bits.
>Could someone sheed some light on this?
>TIA
>Markus|||Markus Zingg (m.zingg@.nct.ch) writes:
> Transact SQL defines that int is:
> Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 -
> 1 (2,147,483,647). Storage size is 4 bytes.
> This implies that only SIGNED integer values are possible with
> SQL-Server. I'm aware that from a data conversion point of view this
> is no problem in that a singed integer can be interpreted as unsigned
> or signed.
> Then, there is a (c) datatype definition SQLUINTEGER as well as
> SQLINTEGER, so unsigned integers seem to be suported conversion wise.
I'm not really sure where you find this SQLUINTEGER type, but if the
type is in C, I presume that the type collection has been defined for
more engines than SQL Server in mind, and some of those engines may
support an unsigned integer type.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx