Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

Building a "rolling" number SP in MS-SQL

Hi,

I am fairly new to MS-SQL and stored procedures.

I need to create a unique continuous "rolling" invoice number which is assigned when I create an invoice. The trouble is that I cannot do:

read current invoice_no
update invoice_no +=1 in a single row table

because I cannot guarantee that 2 users won't pick up the same number and also invoice numbers must be re-used so in the event of a transaction failure e.g.

user 1 gets invoice number 1
user 2 gets invoice number 2

if user 1 fails to update for what ever reason, invoice number 1 will never get used and the VAT man doesn't like missing invoice numbers.

I have thought about having a table which writes a new row when an invoice number is needed e.g.

account        invoice number
MIKE                      1
MIKE                      2
MIKE                      3

so that I can scan to find the last one used, deal with any lock issues and as long as I update it in a transaction along with the invoice creation then it will be O.K. but if it fails I could have:

account        invoice number
MIKE                      1
MIKE                      2
MIKE                      4

I need some way of recognizing that 3 has not been used and the ability to reuse it.


account        invoice number
MIKE                      1
MIKE                      2
MIKE                      4
MIKE                      3

I cannot use identity fields because if I merge databases later, then the identity number could change.

I know that this way will probably get slower in the future but has anyone solved this issue.

Does any of this make any sense?

I would like to write an SP that deals with all this:

1) Locking/dealing with multiple users trying to get the next invoice number.

2)  Handles re-use of "missing" numbers in the event of a transaction failure.

I am sure this problem has been solved many times.

Does anyone have any links for examples of solving this?

Mike
Friday, January 14, 2005

==> read current invoice_no
==> update invoice_no +=1 in a single row table

With the appropriate transaction level set, wrap this inside a BEGIN TRAN, COMMIT and it will do *exactly* what you want.

==> because I cannot guarantee that 2 users won't pick up the same number

If it's inside the above described transaction, that wont happen.

==> and also invoice numbers must be re-used so in the event of a transaction failure e.g.

If it's inside the above described transaction, and there's a failure, it will get rolled back and your invoice numbers will be consistent.


I think you need to (re)read the BOL (Books OnLine) regarding transaction levels and think about how it applies to your situation.

Sgt. Sausage
Friday, January 14, 2005

Use a column with the IDENTITY property set.  Do not attempt to roll this yourself.  You will create serious performance problems -- all transactions that use your "rolling" number will end up serialized.

A.M.
Friday, January 14, 2005

By the way, if you're concerned about merging databases, you might consider either:

A) Using different IDENTITY seeds per database

or

B) Using a GUID instead of an integer

A.M.
Friday, January 14, 2005

Hi,

Thanks for all the answers.

I will read BOL for the transaction levels and I will look into GUID's.

I don't like the idea of identity fields for this although I know it is the easiest option.

Mike
Monday, January 17, 2005

The closest thing I've come across to rolling your own IDENTITY (and I must be a tad blind not to see what the real problem with just using IDENTITY in this case is) is the following:

--create and initialize the current value
create table current_invoice_no(
invoice_no int primary key
)
insert into current_invoice_no(invoice_no)
values(1)

--Now your inserts will look like this
declare @invoice_no int
update current_invoice_no
set invoice_no=@invoice_no=invoice_no+1

insert into invoices(invoice_no, something_else)
values(@invoice_no,'beer')

This does the grab and update in a single atomic operation (on MSSQL anyway) but doesn't do anything about filling in gaps. ('Course, IDENTITY won't fill gaps either.)
But, for better or worse there's a method. Make of it what you will.

Random database developer
Monday, January 17, 2005

I suppose you are right. The real problem is how to generate the number and create the invoice in one transaction.

If this was possible, the "skipping" wouldn't happen.

Mike
Tuesday, January 18, 2005

Hang on. I have just answered my own question.

If I create the invoice in my stored procedure, can I create the header row and read back the id field for use in related tables in one transaction?

Mike
Tuesday, January 18, 2005

Pick any two of the following ...

* high-concurrency
* gap-free sequence
* instant generation of number

If (i) and (ii), then generate the invoices without a number, and use a batch process to assign numbers post-creation. This can run pretty frequently.

If (i) and (iii) then you lose your legal requirement on the gap-free sequence, which won't do.

If (ii) and (iii) then use a locking mechanism to ensure that only one process can be generating invoice numbers at a time (in Oracle, you'd use DBMS_LOCK()). The trick then is to just minimize the amount of time for which the lock is in place, but there's no reason why you still shouldn't be able to generate at a pretty high rate. How fast do you need to generate them?

David Aldridge
Tuesday, January 18, 2005

Gap-free sequence should never be a requirement in a RDBMS.  A surrogate key should not be used for rankings or other purposes that give it "meaning" -- it has no meaning.  It is simply a key.  Be careful when mixing your logical model with surrogate keys, which are really nothing more than physical row identifiers.

A.M.
Tuesday, January 18, 2005

>> Gap-free sequence should never be a requirement in a RDBMS.<<

Meanwhile, back in the real world some companies have legal requirements to implement them. Invoice numbers are an excellent example, and several jurisdictions require that invoice numbers be gap-free.

>> A surrogate key should not be used for rankings or other purposes that give it "meaning" -- it has no meaning.  It is simply a key.  Be careful when mixing your logical model with surrogate keys, which are really nothing more than physical row identifiers. <<

Invoice, and other document, numbers should indeed not be used as primary keys. Especially in a case like this, where they tend to decrease the concurrency of generation of the documents.

David Aldridge
Tuesday, January 18, 2005

David,

"Gap-free sequences" can be generated on the fly for reporting purposes.  There is rarely -- if ever -- a good reason to attempt to materialize those values in a well-designed database.

A.M.
Tuesday, January 18, 2005

>> There is rarely -- if ever -- a good reason to attempt to materialize those values in a well-designed database. <<

Obviously, you can't generate invoice numbers on the fly for reporting purposes. It may be rare that you have to do it, but when there is a legal requirement then unless the authorities that enforce the legal requirement give you an exemption, you _absolutely_have_to_do_it_.

There's no question over this! It's not something you do for synthetic primary keys, or stuff that companies have control over (like customer id numbers), but _somtimes_ you _have_ to do it.

David Aldridge
Wednesday, January 19, 2005

I have now achieved it with an identity field and transactions.

Mike
Thursday, January 20, 2005

Hello everybody, I'm new in database, I was reading your comentes about "Building a "rolling" number SP in MS-SQL "
I know that in this case if we want to set automatically a number in a column and every time that we insert data in the table and we want autamatically to increase the value on that column, it is enought to set that column as indentity and to set the increase value, that is eanoght right? I hope that making in that way there won't be any problem, the values wont be duplicated? or same other problem, please tell me if is this enoght or not and why you would not use this method.
Thank you in advance.

Maverik.
Monday, January 24, 2005

*  Recent Topics

*  Fog Creek Home