It might be a small issue but I need to generate the next invoice number from the field InvoiceNo in the table Invoices.
My problem is a concurrency one since there are more operators adding invoices at the same time and sometimes we have duplicate numbers as yielded by Max(InvoiceNo).
I can't use an Identity field because the InvoiceNo range varies and starts from 1 each year.
The Number is added when the invoice is validated, not when inserted into the table.
One Idea is to Only One Person can add at a time
( Transactions , Locks )
Or Put into seperate table People adding and then add into main table
how you want !!
Why was it decided to start from 1 each year
Could you provide us a sample of invoive numbers? So they all numeric?
It's a common practice to start each year numbering documents with 1.
(Invoices, accounting notes, etc...)
So there's nothing special to provide: 1,2,... and the following year: 1,2,3,...
Sequences seem to be the answer (I still have some 2008R2 servers but we will upgrade).
They also have the Restart option
I didn't know they exist.
Thank you again, Daniel
I would also question the necessity of starting at 1 each year.
Not that it matters but you should also be aware, that like identity, sequences can have gaps:
- if a row fails to commit.
- as any numbers in the cache will be lost if the server goes down.