Dumb question about db primary keys

[MGMorden]

Ok, I've got a database that my code interacts with quite a bit that was setup with an auto-incrementing integer as it's primary key. Now personally, in my own databases I've become a firm believer in using GUID's, but since my code is not the only software hitting this DB I can't change the design.

I know this sounds like an ignorant question, but given that I'm inserting and deleting a decent # of records from this database (on the order of a few hundred thousand per day), I got to thinking: what happens when the primary key being used overflows over what can be stored in a 32-bit integer? Most of the records that are inserted eventually get deleted so there will be plenty of "space" left if the sequence wraps and starts using unused numbers again, but I wasn't sure if that would happen.

Thanks.

EDIT: Gah - sorry, in my haste I forgot to mention which DBMS I was using. I'm on MS SQL Server 2005.

 

[Crusty]

Not sure what the behavior is in MSSQL, but in MySQL the auto-increment will fail to update to the next value and when the row tries to be inserted into the table MySQL will report a duplicate key(assuming the row with the same key hasn't been deleted yet).

However, this is a rather simple thing to test. I would fire up a local copy of MSSQL and create the scenario you described to see the behavior.

 

[KIAman]

I'm not really sure but I know that you can change the type of that IDENTITY from INT to BIGINT on the fly. All the existing values will implicitly convert.

You'll have to check any procedures and functions that reference that IDENTITY field and change the types accordingly.

That should give you a much bigger safety net.