SQL Server 2008 - 无符号整数数据类型

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.

我正在使用SQL SERVER 2008,我在各种表中有一些INT,SMALLINT字段,而且我知道它们都是0或大于0,即我可以将它们取无符号。

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?

如果这是在SQL中使用Unsigned的唯一方法,那么使用它是否有任何缺点/缺点?

3 个解决方案

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

它只是创建一个只能为正的新整数类型,它不会为您提供任何空间节省,否则会因使用无符号字段(这似乎是您的主要目标)而导致。也就是说,unsignedSmallint的最大值与smallint的最大值相同,因此你仍然会浪费那些额外的位(但更多因为你不能插入负值)。

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

也就是说,他们的unsignedInt不允许超过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)

我理解并意识到,在1亿行中,在单个列上使用int32与int64的节省大约为380MB。也许你这样做的最好方法是处理它是为了在你读取它之后抵消你的存储值,理想情况是在视图中并且只从该视图中读取,然后在执行插入时将-2 ^ 31添加到值..但问题是int32的解析发生在插入之前,所以INSTEAD OF触发器将无法工作..(我不知道有什么办法使INSTEAD OF触发器接受不同类型的拥有表的类型)

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.

您将看到值是无符号返回的,但是返回的值是int64而不是unsigned32,因此您的应用程序需要将它们视为仍然是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.

如果你有一个案例,你会看到这样做会有显着的改进(例如表中几乎每列都需要它的两倍),那么上面的努力可能是有保证的,否则我只会留在bigint而不是。

#2

To convert signed smallint to an unsigned number try this:

要将signed smallint转换为无符号数,请尝试以下方法:

CAST(yourSignedSmallInt AS int) & 0xffff

To convert signed int to an unsigned number try

要将signed int转换为无符号数,请尝试

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

例如,如果您的表字段x是smallint并且您想要返回无符号值,那么请尝试

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

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

适当的解决方案取决于您尝试解决的问题。如果这是一个标识字段,并且您的目标是将表可以容纳的行数加倍,而不存储每行使用bigint的4个额外字节,那么只需将字段设置为-2,147,483,648而不是1.如果需要存储大于21.47亿的值,然后使用更大的数据类型。

#1