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



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)

Hi Edhy. Below is my Code for Generating a Unique Key. I decided to go down the route of Locking , and with the limitedted testing i have done so far, it seems to hang together ok. Basically, I have just one table with 3 fields which will hold and maitain all Sequential Numbers:

Uni_PK   Uni_Name     Uni_PrevKey

The Uni_name field is used as a Key to Hold each Seq No. that I need, e.g. InvocieNUmber, DespatchNumber, QuoteNumber etc

I then pass the 'KeyName' as a parameter into the Method. if there is already a key there, it adds one to it, otherwise it adds a new Record and sets it to 1.

As this is my first foray into Locking in SqlServer, there may be somethings that I am missing..

 

public static int GenerateUniqueKey(string myName)
{
MicroFour.StrataFrame.Business.BusinessLayer.TransactionBegin("", IsolationLevel.Serializable);
UniBO bo = new UniBO();
bo.UniFillByTable(myName);
if (bo.Count == 0)
{
    bo.Add();
    bo.UNI_NAME = myName;
    bo.UNI_PREVKEY = 1;
}
else
{
    bo.UNI_PREVKEY = bo.UNI_PREVKEY + 1;
}
bo.Save(true);
MicroFour.StrataFrame.Business.BusinessLayer.TransactionCommit("");
return bo.UNI_PREVKEY} // GenerateUniqueKey (Cannot have more than one Identity Key in SqlServer

Hi Gerard,
Thanks for the code, appreciated!!!

Edhy Rijo