SQL Server 2008 - Unsigned Integer Data Types

Question

I am using SQL SERVER 2008, I have several INT, SMALLINT fields in different tables. And I know that all of them will be 0 or greater than 0, that is, I can take them unsigned.

Is there an easy way to create / use Unsigned data types or do I need to create type-> Make Rule-> Use the Created Type; as indicated in the next 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 a drawback / drawback to using it?

Answer 1

The main (and rather critical) flaw is that it seems that the link you provide does not actually do what you think.

It just makes a new integer type that can only be positive, it does not provide you any space saving that would otherwise result from using an unsigned field (which seems to be your main goal). that is, the maximum value of their unsignedSmallint will be the same as the maximum value for smallint , so you will spend these extra bits anyway (but especially since you cannot insert negative values).

That is, their unsignedInt will not allow values ​​above 2 ^ 31-1.

I understand and understand that in 100 million rows, the savings from using int32 vs int64 in one column is about 380 MB. Perhaps the best way to do this is to process it to compensate for your stored value after reading it, ideally in the view and only ever read from that view, and then insert -2 ^ 31 to the value when pasting .. But then the problem is in that the parsing for int32 happens before the insert, so INSTEAD OF triggers won't work. (I don’t know how to make an INSTEAD OF trigger that accepts different types in terms of ownership of the table)

Instead, your only option in this regard is to use stored procedures for set values, you can either use the view or stored proc to return the value:

 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 that the values ​​returned are unsigned, but the returned values ​​are int64, not unsigned32, so your application will have to process them as if they were still int64.

If you have a case when you see a significant improvement from this (for example, almost every column in the table is twice as large as it should be otherwise), then the above effort may be justified, otherwise I will simply stay instead of bigint .

Answer 2

To convert a signed smallint to an unsigned number, try the following:

 CAST(yourSignedSmallInt AS int) & 0xffff 

To convert a signed int to an unsigned number, try

 CAST(yourSignedInt AS bigint) & 0xffffffff 

for example, if your table field x is small and you want to return unsigned , 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 identification field and your goal is to double the number of rows your table can hold without storing 4 extra bytes with each row to use bigint, then just enter the field at -2147,483,648, not 1 If you need to store values ​​of more than 2.147 billion, then come with a larger data type.