125

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

Improve this question
17
  • 11
    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.... Commented Dec 15, 2010 at 16:59
  • 6
    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? :) Commented Dec 15, 2010 at 19:35
  • 43
    @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. Commented Dec 15, 2010 at 19:43
  • 7
    @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. Commented Dec 15, 2010 at 19:50
  • 22
    @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. Commented Dec 16, 2010 at 17:41

9 Answers 9

Reset to default
91

For that purpose you could use -2,147,483,648 as the seed value.

Identity(-2147483648, 1)
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

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.
good solution...not great....but answers the problem...using the min_value as zero (0)...effectively using the Kelvin scale ;)
This is such a horrible idea
@MatthewRodatus What are the down sides of seeding a new identity column at -2B instead of at 1?
@MatthewRodatus Why is it a horrible idea? Numbers are all relative. If from an early age you'd learned to start counting at -10 and proceed towards zero, you'd be fine with this
|
84

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.

Improve this answer

5 Comments

I guess this is the closest we can get to an answer for this question. Thanks.
If 'proliferation of types' could have saved some space/money, why do you guess this could have been the evil.
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.
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...
@vgru why would you order by ABS? Just start from min value rather than 0
63

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

Improve this answer

3 Comments

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.
Updated link - Jim Hogg's comment on MSSN Office Developers Forum.
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.
4

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.

Improve this answer

2 Comments

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

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:

Id Name
1 John
2 Peter
3 Tom
Improve this answer

1 Comment

Per the documentation, you'll have issues with this if using Informatica: Informatica only supports 16 significant digits, regardless of the precision and scale specified.
1

To convert a signed int to an unsigned number in T-SQL:

SELECT (CAST(x AS bigint) & 0xffffffff)

To convert an unsigned number to a signed int in T-SQL:

Select CAST(-((~CAST(x as bigint) + 1) & 0xffffffff) as int)

These conversions can be very useful. The number must be representable as a UINT32.

Improve this answer

Comments

0

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.

Improve this answer

2 Comments

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.
If you need to jump to BIGINT from INT just because INT does not support unsigned, the waste is 4 bytes per row, not 1 bit.
0

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.

Improve this answer

Comments

0

There are some cases where unsigned numbers are required in SQL server. For example, it may be necessary to store the equivalent of a binary value as an integer. In this case, for the 32-bit binary value, it is necessary to use 64-bit bigint instead of the 32-bit int data type.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.