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?

  • 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
    @Mat It's not the performance hit I'm worried about, it's 4 extra bytes x 153 million = ~612 extra MB wasted, the values go above 3 billion but not 4 billion. A numeric(10) has performace hits in addition to requiring 9 bytes of storage: msdn.microsoft.com/en-us/library/ms187746.aspx – Ehryk Nov 9 '13 at 18:23

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.

  • 3
    "confuse us all even more" - probably refers to everyone using SQL Server, not just their own development team. – Oskar Berggren Aug 1 '17 at 6:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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