Below is my scenario.
I am working for Point Of Sale application. Front end is VB6, back end is SQL Server 2005. My application has an interface to generate cash sale bills. Each sale should be saved with a running cash bill number. For example, 2010/POS/1, 2010/POS/2, 2010/POS/3, 2010/POS/4, and so on. Here, the prefix 2010/POS is constant. It is taken from a table named Prefix. I have to take this prefix and append a running number like 1, 2, 3, 4, and so on.
My problem is how to compute this running number since my server is simultaneously accessed by around 100 sale counters. My idea is to save the Last Bill Number in a table, and to fetch it the next time a bill is raised. Since more client machines fetches this number almost same time, duplicate bill numbers will be computed. How could i lock other machines when one machine processes this value. I also fear that this lock should not introduce more delay to save the bills to server.
Please guide me how to generate unique bill number.
thanks in advance.