[thelist] [SQL] Selecting next available number

[Bruce McIntyre]

You can't quite do what you want, the way you want.

If you supply the number at the *start* of the process, you must save
the number to create a unique idno at the time you get it.

If you supply the number at the *end* of the process, you can ALMOST
guarantee sequential numbers for idno.

There are lots of "tricks" you can do to approximate what you want, but
in the end, there are always ways for the user to bollix up your
requirements.  And all of these tricks can have an impact on performance
that can be considerable, and would get worse as the load gets heavier.

In reality, all you can do is to assign the number at the beginning,
keep it while the user is entering data, and on completion try and save
it.  If the number has also been used by someone else, keep incrementing
it at that point until you find an open number.

Of course, this means that the number assigned at the beginning, and the
number assigned at the end may differ somewhat, and it is still possible
under some circumstances to leave a hole in the sequence.

If your database supports *sequences* and you assign the number at the
end of the process (at the save) then you have the BEST chance of a
unique number.

--
Bruce A. McIntyre
ISS Group (http://www.issgroup.net)
Shaping Visions into Solutions
bmcintyre at issgroup.net
V: 215.942.4718
F: 215.942.4962

-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of Les Lytollis
Sent: Friday, March 04, 2005 5:37 AM
To: thelist at lists.evolt.org
Subject: [thelist] [SQL] Selecting next available number

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
-- 

* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester 
and archives of thelist go to: http://lists.evolt.org 
Workers of the Web, evolt !