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.

In any given database that hold user records in the form of an unique auto-increment field (for the sake of the example, inter-user messages)... what to do when the time comes and it approaches the max signed or unsigned number of the current datatype? (A 32-bit INT)? I'm guessing that the database server would overflow when it tries to assign the (2∧32)-1 number to the next entry, so, how to avoid that happening (without changing the datatype, for the sake of the question) and keep adding records? What would you do?

Why would I use INT's and not, for example, VARCHARS?

It has been several days since I've asked myself this hypothetical question and I would like to know what a professional would do.

share|improve this question

3 Answers 3

up vote 11 down vote accepted

You would generally use integers rather than varchars because they consume less space, have well understood sorting pattern are fast to index etc. Integers are natural data types of a CPU, and hence performance is generally optimal. Typically an integer is 4 bytes, equivalent to just 4 characters in a (non-unicode) varchar.

If you were worried about running out of space with an INT type, then try BIGINT, which gives you 8-byte numbers. The limit on this is pretty huge, and you'd probably run out of disk space before you reached that limit of records :-) The performance of BIGINT is also going to be very good, especially as many servers are now 64-bit too.

The answer to the first part of your question about what happens when you run out in INTs is not simple, especially as you said without changing the datatype to BIGINT. Basically there isn't much you can do, and what you may be able to do is limited very much by the nature of the data in your database. What records are foreign-keyed to this data? Do you still need all the data in that table and the related records? On the assumption that you could archive off a lot of the initial data (and its related data), then the only thing I can suggest is moving the data out of the table (lets say the first 1 to X million records), and then resetting the identity seed to 1. There are all sorts of reasons though I wouldn't recommend it - for example there are many bits of code I have seen that do things like check the maximum value of an id field, to see what has just been added, and that would not work (and shouldn't be done). Also, people assume that record N was created before N+1. No easy answer I think.

Finally, I don't know about MySQL, but SQL Server would give an overflow error if you reached the limit.

share|improve this answer
1  
I am pleased with such a detailed answer. Thanks for the explanation of the VARCHAR, INT and BIGINT deal. Since the question is hypothetical, I wonder what would happen if also the BIGINT limit is reached. The question was raised by a post I saw about facebook using INT's and reaching the limit, and I see it as totally possible. Archiving would work, or creating a second table with a conditional statement (which, as you said, would require scripts to be updated, too, and it would be pretty complex). Overall, great answer. I appreciate the time taken. –  AeroCross Jan 30 '11 at 17:39

One overlooked point is that many folk start the auto number or identity at 1 thus losing half of the possible range immediately (for signed)

You'd simply redefine the number to start at -1, increment -1 in this case.

Arguably, if you ever expected to fill your identity column then you should have designed this in and used a wider datatype at the start.

See this recent question oon SO: SQL Server 2008: what happened if identity oversteps a maximal value of int?

share|improve this answer
    
It's logical I'd use a wider datatype (for a table that would how THAT amount of data), but since it was an hypothetical question, I wanted some insight. If it's signed, that may work (but I'd be a little weird having a primary key with negative numbers, IMHO) and I think it's pretty clever. It'd give time for the DBA to archive the positive data and start again. If unsigned, well... problems. –  AeroCross Jan 30 '11 at 17:38
    
Alternatively to using a -1 increment from -1, start at (-2147483648) and increment by 1. But yeah, after you cross INT_MAX then you're pretty well hosed and need to revisit the design, and remove the old index replacing it with a new larger one. and if you pass unsigned BIGINT then I want to come work on your team ;) –  jcolebrand Jan 31 '11 at 14:29
    
PostgreSQL uses sequences to generate id numbers; the CREATE SEQUENCE statement lets you specify CYCLE, which will just wrap around if you reach the maximum value. (Or the minimum value, if you're going the other direction.) The CYCLE option is in the SQL standards now. (Since at least 2003.) –  Mike Sherrill 'Cat Recall' Jun 10 '11 at 0:47

Overflow BIGINT? Haha. First figure out how to achieve immortality. INT UNSIGNED (4 billion) is hard enough to reach. 100 INSERTs per second would come close to overflowing INT in a year. BIGINT would take several billion years.

To fix: ALTER TABLE foo MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT; But that will take hours because it will copy over the table (which has close to 4 billion rows, right?) and rebuild all the secondary indexes. Plan Ahea d.

Generally when you try to store a number too big for a field (eg, 999 in a TINYINT UNSIGNED), it will silently cap it to the max for the field (255 in this case). There may be a "Warning", but most people don't bother to check warnings. If it is a UNIQUE field, or there are FOREIGN KEYS, you might get a more serious error.

CHAR or VARCHAR is silently truncated to the space available.

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.