Why Doesn't SQL Server Support Unsigned Datatype

Why doesn't SQL Server support unsigned datatype?

If I had to guess, I would say that they are trying to avoid a proliferation of types. Generally speaking there isn't anything that an unsigned integer can do that a signed integer can't do. As for the case when you need a number between 2147483648 and 4294967296 you probably should go to an 8 byte integer since the number will also eventually exceed 4294967296.

4 byte unsigned int in SQL Server?

It doesn't seem so.

Here's an article describing how to create your own rules restricting an int to positive values. But that doesn't grant you positive values above 2^31-1.

http://www.julian-kuiters.id.au/article.php/sqlserver2005-unsigned-integer

How to work around unsupported unsigned integer field types in MS SQL?

When is the problem likely to become a real issue?

Given current growth rates, how soon do you expect signed integer overflow to happen in the MS SQL version?

Be pessimistic.

How long do you expect the application to live?

Do you still think the factor of 2 difference is something you should worry about?

(I have no idea what the answers are, but I think we should be sure that we really have a problem before searching any harder for a solution)

SQL Server 2008 – Unsigned Integer Data Types

The main (and rather critical) disadvantage is that it seems that the link you provide doesn't actually do what you think it does.

It merely just makes an new integer type that can only be positive, it doesn't provide you with any space saving that would otherwise result from using an unsigned field (which seems to be your main aim). that is to say that the max value of their unsignedSmallint would be the same as the max value for smallint, you would therefore still be wasting those extra Bits (but more so since you can't insert negative values).

That is to say that their unsignedInt would not allow values above 2^31-1.

I understand and appreciate that in 100 million rows the savings from using a int32 vs int64 on a single column is around 380MB. Perhaps the best way for you to do this is to handle this is to offset your stored value after you read it, ideally within a view and only ever read from that view, and then when doing an insert add -2^31 to the value.. But the problem then is that the parsing for int32 occurs before the insert so INSTEAD OF triggers won't work.. (I do not know of any way to make an INSTEAD OF trigger that accepts different types to that of the owning table)

Instead your only option in this regard is to use stored procedures to set the value, you can then either use a view or a stored proc to get the value back:

create table foo
(fooA int)
GO

CREATE VIEW [bar]
AS
SELECT CAST(fooA AS BIGINT) + 2147483647 AS fooA
FROM foo
GO

CREATE PROCEDURE set_foo
    @fooA bigint
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF @fooA < 4294967296 AND @fooA >= 0
        INSERT INTO foo VALUES (@fooA - 2147483647)
    --ELSE
        -- throw some message here
END
GO

This can be tested using:

exec set_foo 123
exec set_foo 555
select * FROM bar
select * FROM foo
exec set_foo 0
exec set_foo 2147483648
exec set_foo 4147483648
select * FROM bar
select * FROM foo

You will see the values are returned unsigned, however the returned values are int64 and not unsigned32 so your application will need to treat them as if they were still int64.

If you have a case where you will see significant improvement from doing this (such as almost every column in the table is twice as big as it otherwise needs to be) then the effort above might be warranted, otherwise I would just stay with bigint instead.

Unsigned integer datatype in column-oriented DBMS

I found one answer on the "How to Migrate from SQL Server" (page4) at Infobright.org:

UNSIGNED INTEGERS – Unsigned integers
have historically been selected by
DBAs and database designers to provide
capacity for larger maximum values for
a given integer field than is possible
with a signed integer. Where negative
values do not exist in the data, or
are not allowed, the approach of
selecting unsigned integers allowed
accommodation of larger values while
selecting smaller data types in
traditional row-oriented technologies.
In Infobright’s case, when unneeded
bytes exist for a particular integer
value, they are “squeezed” out by the
inherent compression algorithms. For
this reason, Infobright recommends
selecting the next-larger integer data
type – for example, BIGINT over
INTEGER, or MEDIUMINT over SMALLINT –
such that the maximum column value can
still be accommodated in the chosen
data type. Infobright doesn’t suffer
the wasted space consequences of
“over- typing” one’s columns.

Sql server datatypes , for storing a Positive number

Tinyint

A SQL Server system datatype, it is a tiny integer column that holds
whole numbers between 0 and 255, inclusive. Storage size is 1 byte.

The TinyInt allow you to insert Positive numbrs only but you can have values between 0 and 255...

See Demo here

SQL SERVER NUMERIC DATATYPE

Why unsigned integer is not available in PostgreSQL?

It is already answered why postgresql lacks unsigned types. However I would suggest to use domains for unsigned types.

http://www.postgresql.org/docs/9.4/static/sql-createdomain.html

 CREATE DOMAIN name [ AS ] data_type
    [ COLLATE collation ]
    [ DEFAULT expression ]
    [ constraint [ ... ] ]
 where constraint is:
 [ CONSTRAINT constraint_name ]
 { NOT NULL | NULL | CHECK (expression) }

Domain is like a type but with an additional constraint.

For an concrete example you could use

CREATE DOMAIN uint2 AS int4
   CHECK(VALUE >= 0 AND VALUE < 65536);

Here is what psql gives when I try to abuse the type.

DS1=# select (346346 :: uint2);

ERROR: value for domain uint2 violates check constraint "uint2_check"

When should I use UNSIGNED and SIGNED INT in MySQL?

UNSIGNED only stores positive numbers (or zero). On the other hand, signed can store negative numbers (i.e., may have a negative sign).

Here's a table of the ranges of values each INTEGER type can store:

^{Source: http://dev.mysql.com/doc/refman/5.6/en/integer-types.html}

UNSIGNED ranges from 0 to n, while signed ranges from about -n/2 to n/2.

In this case, you have an AUTO_INCREMENT ID column, so you would not have negatives. Thus, use UNSIGNED. If you do not use UNSIGNED for the AUTO_INCREMENT column, your maximum possible value will be half as high (and the negative half of the value range would go unused).

Is it bad practice to use MySQL unsigned integers?

I don't think it is a bad practice to use data types that are appropriate to the data being stored.

You bring up a very good issue about portability to other databases. If this is a concern, then you should try to adhere either to ANSI standard functionality or to functionality that you know is similar between the databases.

Moving code from MySQL to SQL Server can involve a lot of more significant effort than just the difference between signed and unsigned integers. There are numerous places where functions are different, non-existent, or behave different. Just to give a smattering of examples:

• cast() takes different types in MySQL versus SQL Server (as signed vs as int)
• MySQL has silent error handling in the case of 123 + '456b' whereas SQL Server will produce an error.
• The string concatenation operator is different in the two databases.
• datediff() takes different arguments in the two databases

So, the difference between signed and unsigned will be a minor concern in a conversion. You will need to write your code carefully if you are planning such a change.

---

Related Topics