SQL Server 2008 – Unsigned Integer Data Types

Question

I am using SQL SERVER 2008, I have a number of INT, SMALLINT fields in my various tables, And I know they all will be 0 or greater than 0 i.e. I can take them Unsigned.

Is there a simple way of creating/using Unsigned data types OR will I have to Create type->Make Rule->Use the Created Type; as specified in the following article?

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

If this is the only way to use Unsigned in SQL, is there any disadvantage/drawback of using it?

Answer 1

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.

Answer 2

To convert signed smallint to an unsigned number try this:

CAST(yourSignedSmallInt AS int) & 0xffff

To convert signed int to an unsigned number try

CAST(yourSignedInt AS bigint) & 0xffffffff

for example if your table field x is a smallint and you want to return the unsigned value then try

SELECT (CAST(x AS int) & 0xffff) FROM ... WHERE ....

Answer 3

The appropriate solution depends on the problem you are trying to solve. If this is an identity field, and your objective is to double the number of rows your table can hold without storing 4 additional bytes with each row to use a bigint, then just seed the field at -2,147,483,648 rather than 1. If you need to store values greater than 2.147 billion, then go with a larger data type.