为什么 SQLServer 不支持无符号数据类型？

我特别考虑的是未签名的 int。

这里有一个实际的例子: 当您的标识列达到最大值时，您会做什么？可以使用 BigInt(8字节存储而不是4字节存储)或者重构应用程序以支持负整数，甚至可以按照 这个答案中的指示创建自己的规则; 这两种选项都不是最优的。

UInt将是一个理想的解决方案，但是 SQLServer 没有提供它(MySQL 提供这种解决方案的地方)。

我知道无符号数据类型不是 SQL 标准(SQL-2003)的一部分，但对我来说仍然是一种浪费。

为什么不包括这些(在 SQLServer 中或在标准中) ？

89120 次浏览
小开
最佳答案

If I had to guess, I would say that they are trying to avoid a proliferation of types. Generally speaking there isn't anything that an unsigned integer can do that a signed integer can't do. As for the case when you need a number between 2147483648 and 4294967296 you probably should go to an 8 byte integer since the number will also eventually exceed 4294967296.

小开

For that purpose you could use -2,147,483,648 as the seed value.

Identity(-2147483648, 1)
小开

I found a similar question on Microsoft Office Dev Center.

The reply from Jim Hogg (Program Manager) has some pro's and con's for adding unsigned int's. The major con is the rules to implement implicit type conversions become a nightmare to get right.

The request was closed as "Won't Fix".

小开

They don't support the SIGNED and UNSIGNED keyword because they're not standard. In SQL standard, all numeric types are signed.

UNSIGNED (and SIGNED, which is the default) are MySQL extensions that can be useful to store higher unsigned numbers in the same amount of bytes, and disallow negative numbers.

小开

Take 32 bit(8 byte) int for example. The range of 32 bit int is from -2^31 to 2^31-1. It takes 31 bit to record the value you assigned and only 1 bit to record the sign of value.

So the answer to your question is "Unnecessary". Even though every value you assigned is positive, it waste only 1 bit per value. Creating a new datatype for saving only 1 bit per value is not a good way to optimize storage space.

小开

Set your DB to have the min identity Identity(-2147483648, 1)

Then when loading into your .net UInt64 variable add 2147483648 to it. then -2147483648 becomes 0 -1000000000 becomes 1147483648

  • But also in most cases the internal keys shouldn't be exposed to clients, I typically use a separate key which can be anything like 'ABCKey1"

However I'm in agreement that the datatype is large enough in 99% of the systems out there. If you really need more you could use a GUID - however that sucks for Index's unless you use the next sequential GUID.

小开

There are some cases where unsigned numbers are required in SQL server. For example, it may be necessary to store the equivalent of a binary value as an integer. In this case, for the 32-bit binary value, it is necessary to use 64-bit bigint instead of the 32-bit int data type.


提交答案