[thelist] [SQL] Selecting next available number

[Les Lytollis]

Hi All

I am sure there is some accepted way of dealing with this but any
pointers would be appreciated. 

I need to supply a unique id for display on screen at the *start* of an
order process. The id should be the next available within a range, eg
1234000000 to 1234999999. 

I currently do this by finding the max(id) for that range in a db table
and adding one.

I have two issues which I am not sure how to deal with.

If I add the id to the db at the end of the process, how do I ensure
that the same id is not generated again (for a different customer)
before it is added to the db? Eg customer 1 is given id 111, customer 2
starts an order before customer 1 has completed, so Max(id) is still
110. 

If I add the id to the db at the start of the process, how do I select
those id's from abandoned transactions that have "gone back into the
pool"?
Eg 111 was abandoned, 112 was used in the meantime, since select Max(Id)
will now bring back 112,  how to I select 111 as being available for use
again?

Platform is c#, sqlserver.

TIA
Les Lytollis