patternsqlMajor
Why aren't unsigned integer types available in the top database platforms?
Viewed 0 times
whythetopavailableunsignedarendatabaseplatformstypesinteger
Problem
Databases are usually very customizable with varying data types and custom lengths.
It surprises me, as I try to look for the syntax to use
This seems like a glaring omission on their part - the next best perfomant option being a long/bigint, (8 byte integer), but could be completely unneccessary! Does anyone know why they would choose to not include native unsigned int support?
It surprises me, as I try to look for the syntax to use
unsigned int types that they are not available from neither PostgreSQL and MS SQL Server. MySQL and Oracle seem to.This seems like a glaring omission on their part - the next best perfomant option being a long/bigint, (8 byte integer), but could be completely unneccessary! Does anyone know why they would choose to not include native unsigned int support?
Solution
Microsoft's Jim Hogg has responded to this issue with the following:
There are pros and cons. On the pro side, it seems like a good way to avoid some errors - having to check a (signed) int has value > 0. And I would also venture that many uses of int in fact relate to counts that should never be negative anyway. On the question of doubling max row count? - true, but I would say this is less compelling.
On the cons side ... mixing signed/unsigned types in C or C++ seems like it should be simple enough. It's not. It opens a small tarpit of hard-to-find mistakes - most due to the complex rules for implicit promotions/widenings. SQL, alas, already has an even more complex set of implicits casting rules. Adding unsigned ints, I fear, would confuse us all even more.
I'll keep this suggestion on the books. But, among all the features we could/should be adding, this one, with respect, is not near the top of that list.
Source: Microsoft Connect
I would add significantly to the pro list, and reiterate that their SQL engine is already doing FAR more complex things than this, and so their team can handle the added complexity. While I don't agree with their summation, this is Why SQL Server doesn't support unsigned types.
The Connect link was originally posted by Martin Smith in the question comments.
There are pros and cons. On the pro side, it seems like a good way to avoid some errors - having to check a (signed) int has value > 0. And I would also venture that many uses of int in fact relate to counts that should never be negative anyway. On the question of doubling max row count? - true, but I would say this is less compelling.
On the cons side ... mixing signed/unsigned types in C or C++ seems like it should be simple enough. It's not. It opens a small tarpit of hard-to-find mistakes - most due to the complex rules for implicit promotions/widenings. SQL, alas, already has an even more complex set of implicits casting rules. Adding unsigned ints, I fear, would confuse us all even more.
I'll keep this suggestion on the books. But, among all the features we could/should be adding, this one, with respect, is not near the top of that list.
Source: Microsoft Connect
I would add significantly to the pro list, and reiterate that their SQL engine is already doing FAR more complex things than this, and so their team can handle the added complexity. While I don't agree with their summation, this is Why SQL Server doesn't support unsigned types.
The Connect link was originally posted by Martin Smith in the question comments.
Context
StackExchange Database Administrators Q#53050, answer score: 20
Revisions (0)
No revisions yet.