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)GOCREATE VIEW [bar]ASSELECT CAST(fooA AS BIGINT) + 2147483647 AS fooAFROM fooGOCREATE PROCEDURE set_foo    @fooA bigintASBEGIN    SET NOCOUNT ON;    -- Insert statements for procedure here    IF @fooA < 4294967296 AND @fooA >= 0        INSERT INTO foo VALUES (@fooA - 2147483647)    --ELSE        -- throw some message hereENDGO

This can be tested using:

exec set_foo 123exec set_foo 555select * FROM barselect * FROM fooexec set_foo 0exec set_foo 2147483648exec set_foo 4147483648select * FROM barselect * 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.

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 ....

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.