SQL Server 2008 – 无符号整数数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7267233/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2008 – Unsigned Integer Data Types
提问by user899055
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，即我可以将它们设为 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
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 的唯一方法，那么使用它有什么缺点/缺点吗？
回答by Seph
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 unsignedSmallintwould 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 unsignedIntwould 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 OFtriggers 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 setthe value, you can then either use a view or a stored proc to get the value back:
相反，您在这方面的唯一选择是对set值使用存储过程，然后您可以使用视图或存储过程来取回值：
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 bigintinstead.
如果您有这样的情况，您将看到这样做的显着改进（例如，表中几乎每一列的大小都是原本需要的两倍），那么上述努力可能是有道理的，否则我会坚持下去bigint。
回答by boston engineer
To convert signed smallint to an unsigned number try this:
要将有符号 smallint 转换为无符号数字，请尝试以下操作：
CAST(yourSignedSmallInt AS int) & 0xffff
To convert signed int to an unsigned number try
要将有符号 int 转换为无符号数字，请尝试
CAST(yourSignedInt AS bigint) & 0xffffffff
for example if your table field x is a smallint and you want to return the unsignedvalue then try
例如，如果您的表字段 x 是 smallint 并且您想返回该unsigned值，请尝试
SELECT (CAST(x AS int) & 0xffff) FROM ... WHERE ....
回答by Tim F
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.
适当的解决方案取决于您要解决的问题。如果这是一个标识字段，并且您的目标是将您的表可以容纳的行数增加一倍，而不用每行存储 4 个额外的字节来使用 bigint，那么只需将该字段的种子设置为 -2,147,483,648 而不是 1。如果您需要存储大于 21.47 亿的值，然后使用更大的数据类型。
