21

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?

Improve this question
12
  • 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, 2013 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, 2013 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, 2013 at 10:58
  • 2
    For SQL Server an explanation is here
    – Martin Smith
    Nov 9, 2013 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, 2013 at 18:23

2 Answers 2

Reset to default
20

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.

Improve this answer
2
  • 3
    "confuse us all even more" - probably refers to everyone using SQL Server, not just their own development team.
    – Oskar Berggren
    Aug 1, 2017 at 6:52
  • 1
    I wish they'd have a SET NO_1970s_SQL_BOLLOCKS ON option to SQL Server so we can dispense with unneeded implicit narrowing conversions. Is the aim of ISO SQL compliance still worthwhile at all considering how horrible ISO SQL is nowadays, and the fact there has never been portability for non-trivial queries between RDBMS? Meanwhile it's 2021 and the lack of support for deferrable-constraints, macros, and sane change-tracking, let alone unsigned integers, is driving me up the wall :(
    – Dai
    Feb 19, 2021 at 23:24
1

I know this thread is very old; the OP mentioned the need for unsigned int for probably one of two reasons.

  1. the values recorded are only positive and thus a potential storage saving from using a smaller type (bitwise)
  2. it's an identity an the OP is trying to reduce the key size.

This previously perplexed me as well, my work-around for (1) was to use views and SP's to ensure that the DB stores the smaller value but that the representation always adjust for it – not ideal but the space saving offset the cost of CPU cycles.

For (2) I've simply always started my identity seed at the greatest negative number -32768 for a smallint – still not ideal but it works and doubles the keyspace available vs starting from the traditional counter of 1.

Improve this answer
2
  • Thanks for the input! I was asking as I was looking to store a lot of values that ranged from 0 to 2,851,254,831 and this is just north of the upper limit for signed 32 bit integers - 2,147,483,547. An unsigned 32 bit integer would store this just fine, as the maximum is 4,294,967,295. By storing as a signed Int32 and then converting out when I want the unsigned, I can indeed keep my storage space down. And I never thought about starting my identity columns negative before!
    – Ehryk
    Apr 28, 2020 at 17:25
  • how about 64b bigint? There is no bigger datatype to store and process unsigned 64b.
    – Alexis
    Jul 22, 2021 at 5:22

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.