Next document number

Transact-SQL
1

Hello,
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.

Thank you,
Daniel

2
3

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

4

Why was it decided to start from 1 each year

Could you provide us a sample of invoive numbers? So they all numeric?

5

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

6

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

7

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:

  1. if a row fails to commit.
  2. as any numbers in the cache will be lost if the server goes down.
1 Like