I have a table which has a number of Sequential Unique fields (e.g. Invoice Number, Despatch Number, Order Numer etc) whcih all need to be just one higher than the previous one, but can all be different. My first idea was to use Identity keys, but Sql Server does not suport more than one 'Identity' keys in the one table.
I've written a method whcih updates a 'Uniques' table which just has 3 fields: PK,UniqueName,NextKey. I then use this to give me a unique key, depending on the UniqueName...all working fine. I dish out these by using a SFrame BO and code is as follows (Pseudo Code):
Select out from the table based on the UniqueName
If BO.Count = 0
{ Add a new record to BO and set Key to 1}
Else
{ Increase Existing Key by 1}
BO.Save
Return Thekey
Above working fine... but I am concerned that if there are say 60 different users getting a New Number, there is the possibility that 2 users may get the same number. I know I can put a Unique Index on the Field... this will give an error for duplicates but was looking for a more Fail-Safe way.
Is there any 'sure' way of preventing this, or another more secure way of dishing out numbers (want to keep all numbers in the one table)