Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 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?

share|improve this question
    
Why would you want to use an unsigned int? (The only portable guarantee you have on that type in C is that it can hold numbers up to 65535.) –  Mat Nov 9 '13 at 9:44
2  
Portable == standard mandated. The C standard does not specify the width of ordinary ints or longs, just minimum ranges of representable numbers. Platforms with 16bit ints were common at some point. 64bit is possible. 36 too (though extinct). 24 happens (DSPs). How often is it that you have data the fits in 32bit but not 31, and that you've measured that using ordinary numeric types gives you a performance hit? –  Mat Nov 9 '13 at 10:24
2  
Both SQL-Server and Postgres has NUMERIC(10) which allows integers up to 9.999.999.999 (and with a constraint you can disallow negative values.) –  ypercube Nov 9 '13 at 10:50
4  
For one reason: they are not specified in the SQL standard. For a more lengthy discussion regarding Postgres take a look at this discussion: postgresql.1045698.n5.nabble.com/… and this: postgresql.1045698.n5.nabble.com/… –  a_horse_with_no_name Nov 9 '13 at 10:58
2  
For SQL Server an explanation is here –  Martin Smith Nov 9 '13 at 12:16

1 Answer 1

up vote 0 down vote accepted

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] (https://connect.microsoft.com/SQLServer/feedback/details/515502/unsigned-integer-data-type)

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 was posted by @MartinSmith in the comments.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.