Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

I am specifically thinking about unsigned int .

Here is a practical example: what do you do when your identity column maxes out? It's possible to either go BigInt (8 bytes storage instead of 4) or to refactor the application to support negative integers, and even to create your own rules as indicated in this answer ; neither of those options are optimal.

UInt would be an ideal solution, but SQL Server does not offer it (where MySQL does).

I understand that unsigned datatypes are not part of the SQL standard (SQL-2003) but still seems like a waste to me.

What is the reason of not including these (in SQL Server or in the standard)?

Ask the SQL Server design team..... also: are you really gonna max out even 2 BILLION INT IDENTITY values?? REALLY?!?!?! If you have more than 2 billion rows of whatever it is you're dealing with, I bet you can spare some disk space and use a BIGINT as IDENTITY.... – marc_s Dec 15, 2010 at 16:59 What do you mean marc_s? That's only an insert every 800ms for 50 years straight, your tables don't have that kind of activity? :) – Mike M. Dec 15, 2010 at 19:35 @Mike M: Not all of us work on mickey mouse apps... we've used 3 billion+ of a bigint in under 2 years. Peak is > 2000 rows per second. – gbn Dec 15, 2010 at 19:43 @gbn I didn't mean to imply that no one had that load. However, as has been said, if you DO have > 2000 rows per second, an extra 2B isn't going to help your cause. – Mike M. Dec 15, 2010 at 19:50 @Mike M and @marc_s, if I was working on a system with 2 billion rows table, I may pay attention to wasted storage. I may pay attention to the index page size and to the index scan performance. In such conditions, I would like not wasting space. – Romhein Dec 16, 2010 at 17:41 Ha I absolutely love this answer. I'm not sure I'd ever implement it but it definitely solved the issue of having half your ids not used. – Kevin Dec 8, 2020 at 20:22 good solution...not great....but answers the problem...using the min_value as zero (0)...effectively using the Kelvin scale ;) – GoldBishop Mar 18, 2021 at 0:19

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.

If 'proliferation of types' could have saved some space/money, why do you guess this could have been the evil. – Samuel Jul 17, 2013 at 4:12 Fetching rows by their value will also be slower (i.e. ORDER BY ABS(Id) ), especially if the column is a clustered primary key. For example, using a 32-bit unix timestamp is often a handy way to shave 4 bytes off a standard SQL datetime. – vgru Oct 31, 2014 at 9:31 Today I'm facing with the task of converting the endianess of 32-bit unsigned numbers stored as strings directly in SQL Server. It would be trivial to achieve using BINARY(4) and reverse if SQL Server accepted unsigned integers... – Loudenvier Mar 13 at 12:32

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".

The link is not working anymore, so I can't read the original answer. But I believe that the problem is not that it's a nightmare; it's that there is not a standard which says how to do it. They could do what MySQL does for example (I don't think other DBMSs support UNSIGNED), but if another DBMS adds support for signs, they could use different rules. Conversion design is an important matter. JavaScript is an example of what happens when it's not taken seriously. – Federico Razzoli Feb 6, 2020 at 18:04 I have a strong need for unsigned integer types because they represent the nature of the data being stored more accurately. I fully understand the concerns about implicit conversion, but that shouldn't be a problem because implicit conversion to any unsigned type from a signed type shouldn't be allowed - which neatly solves that problem (I'm assuming that ISO SQL requires it?). Ultimately my frustration is that as applications devs we're told to model our data as close to the business-domain as possible, but then we're hamstrung by our tools that stop us from doing that. – Dai Feb 19, 2021 at 23:10

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.

"In SQL standard all numeric types are signed" - yes, but ISO SQL is painfully at-odds with the day-to-day challenges of data-modelling and application development. It becomes an absolute necessity to violate the ISO SQL spec in order to ship something usable. – Dai Feb 19, 2021 at 23:13 UNSIGNED types are desirable, but not that important. Most DBMSs don't support them, which keeps type conversions (and therefore operations between different types) much simpler. If you want to avoid negative numbers, add a CHECK constraint. – Federico Razzoli Feb 20, 2021 at 12:09

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.

Sometimes there are other reasons than just storage space. If I want to use unsigned values in a program (and many languages support such a thing for good reason), I shouldn't have to perform a conversion to retrieve/store those values in a database. Someone then looking at the raw data has to adjust the values manually or mentally to really visualize the state in the program. Usability and utility are very important factors that seem to have gone completely ignored by the SQL Server team in this case. – IAmJersh May 3, 2022 at 14:42

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.

You can always use DECIMAL . Huge decimal - DECIMAL(38, 0) . Should suffice for a month or two...

CREATE TABLE IdentityTest
    Id DECIMAL(38, 0) IDENTITY,
    Name NVARCHAR(200)
INSERT INTO IdentityTest VALUES('John'),('Peter'),('Tom')
SELECT * FROM IdentityTest
DROP TABLE IdentityTest
Which produces this result:
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.