5

The titled basically states it. If I have a bigint column with primary key and autonumber, and a record is deleted, will SQL Server ever reuse that now available value?

The underlying issue is that I have a multi-user environment, and if I retrieve a record from the database into memory, then another user makes changes (like deleting and/or re-adding), if I do updates or deletes based on that ID in memory, is there a chance that I could be editing a record that was added in the background that doesn't truly represent the record I should be editing?

I realize I could use Guids, but I'd really like to know if BigInt/Autonumber is "safe" in this scenario.

Thanks!

jwatts1980
  • 325
  • You worried about running out of numbers or is there some other reason for continuous numbering? Consider using a soft delete. – JeffO Jul 26 '13 at 16:00
  • Not concerned with the number of numbers, it's a question of uniqueness. – jwatts1980 Jul 27 '13 at 17:37

2 Answers2

4

There's no such thing as AUTONUMBER in SQL Server, that I'm aware of. What there is, is the IDENTITY property, which allows for the automatic creation of new values, such as for primary keys. And IDENTITY is just a generator: it starts at a starting value and counts upwards.

There are some advanced uses to make it loop, but you have to know what you're doing to invoke those. So no, if you just use a normal IDENTITY 1, 1 on a column, and it's a BIGINT, for all intents and purposes it will never reuse a value. (If you're able to exhaust the 64-bit range of numbers, that's a different problem...)

Mason Wheeler
  • 82,789
  • Yeah, sorry.. I was working with a MySQL database earlier today, and I was thinking autonumber... but yes, Identity is what I meant. – jwatts1980 Jul 25 '13 at 22:12
1

wouldn't it be simple just to try it out? To answer your question, auto-increments typically use sequence generators to come up with numbers. Once a sequence generator gives you a number, it will move on and will continue giving new numbers regardless of what happens to the existing tables. So you should be safe.

DXM
  • 19,992
  • In my experience with MySQL, it will reuse numbers in an autonumber field. But it hasn't seemed to be exactly predictable. I didn't think that simply creating some records, deleting them, then creating more would really give me a definitive answer. – jwatts1980 Jul 25 '13 at 22:14
  • 2
    Withy mysql and innodb databases, the last autoincrement is stored on memory (not on disk). So if you create a record and it gives you ID=1234, you delete it, and then create another record, it will get ID=1235. Because it had the last one in memory. But if you were to restart the server between the deletion of 1234 and the insertion of another record, the new record would get ID=MAX(ID)+1, so repeating 1234 (assuming 1233 was still there) – Carlos Campderrós Jul 26 '13 at 10:23
  • 1
    For MySQL's myisam databases if I recall correctly the last autoincrement was stored on disk, so no problems there even if you restart MySQL. – Carlos Campderrós Jul 26 '13 at 10:24