How to get the next available number in sequence?

Question

Table has a field that is a counter.
When the record is deleted, the counter number becomes available again.
New records must use the lowest available "slot" for counter.

Example:

Material | Counter    
00AF10 | 02    
00AF11 | 03    
00AF12 | 04

In this case, a new inserted record will take the counter number "01" and a new record after that, the counter number "05".

I've tried doing a select max counter + 1 as a new record, but that will of course fail the requirement of utilizing available "slots" in the counter sequence. I'm pulling an all nighter and my brain is fried. Can you help?

Don't do this!!! It will be a performance nightmare. Is it really important that these values have no gaps? — Jodrell, Apr 24 '19 at 15:17
Use identity column to generate incrementing numbers (with possible gaps)... use row_number over order by identity in your reports to generate gapless numbers. — Salman A, Apr 24 '19 at 15:22
@duesca, does the business also mandate that, once assigned to a row, these values cannot change? Have you asked, would you ever like to undelete? — Jodrell, Apr 24 '19 at 15:24
What do you want to happen if you `DELETE` a row? re-number every row and possibly break everything as the referential integrity falls to pieces? — Thom A, Apr 24 '19 at 15:24
You often find that "business demands", and business needs are different things. They very likely don't need it, and it's a "nice to have", but only for superfluous reasons. More than often the value of the `IDENTITY` column isn't even exposed to the users, so it value is utterly meaningless to anything other than the database engine. — Thom A, Apr 24 '19 at 15:27
Is Counter the PK for the table? Or is it just an arbitrary numerical sequence? If it's the PK, simply don't do this! If it's not, @salmanA has recommended the correct approach. — Krishna Gupta, Apr 24 '19 at 15:37

score 1 · Answer 1 · answered Apr 24 '19 at 15:33

You shouldn't do this, its a bad idea, not just technically but also business wise it makes little sense and offers no real benefit.

However, if you really have to do it, and getting a different job is not an option.

I'd make a new table with two columns, one column for the contiguous values, another, a foreign key to you your other data, that may get deleted.

Then you can handle deletions and inserts with hideous transnational triggers, but at least you'll be able to index your new table appropriately.

How to get the next available number in sequence?

1 Answers1