Create Unique Invoice Number in SQL
One of my friends was still using the MAX() SQL function to get the latest Invoice number. That Invoice Number field did not even had a clustered index.
So far he never had any problems. When I asked him how many users are using his application he said maximum of 10 users. Well he was really lucky that did NOT get any duplicate invoice number. If he had 500 users hammering his system say in 10 minutes interval then there could be a real problem.
Not using a clustered index on the Invoice Number also escalated the problem since in a regular index the Invoice numbers are not even adjacent to each other and MAX() function needs to go all over to find the highest number. A clustered index on the Invoice number was not created since it was used already on the Primary Key. You can only have one clustered index per table.
But the whole concept was wrong from the get go.
Call us for Free Consultation for Remote DBA services at our SQL Consulting Firm at: 732-536-4765
A better solution will have a separate table so you can generate Invoice numbers. This way you let IDENTITY field work for you with any Insert as shown in the below Stored Procedure.
Let’s create the InvoiceNumbers table
The below Stored Procedure should be called whenever a new Invoice is needed. Even if 500 people will INSERT a new record at the same time interval you always guaranteed to get a Unique Invoice ID while using the Identity field for that purpose. The SCOPE_IDENTITY() will grab that value for you in the current session.
Note that the SCOPE_IDENTITY() also works fine within triggers as well and get the correct value while using the Identity field.
Check our Custom Software Development Services.
For SQL Server 2012 you will need to use trace flag 272 to prevent any gaps when you restart SQL instant. Other versions such as: sql server 2008 and below can use the above code without any addtional changes.
In addition, SQL Server 2012 introduced the capability to Generate Sequence Numbers. You may read that article as well if need be.
I started to develop custom software since 1985 while using dBase III from Aston Tate. From there I moved to FoxBase and to FoxPro and ended up working with Visual FoxPro until Microsoft stopped supporting that great engine. With the Visual FoxPro, I developed the VisualRep which is Report and Query Engine. We are also a dot net development company, and one of our projects is a web scrapping from different web sites. We are Alpha AnyWhere developers, and the Avis Car Rental company trusted us with their contract management software that we developed with the Alpha Five software Engine.
Comments
The code is useful but has limitations; It fails if the application deals with different types of invoices (Purchases, Sales, Returns, Damage,..., etc.) and each type of invoices should have separate sequential numbers for each different store.
As your code is totally depending on identity, each type of invoices for each store in the system need a separate table.
But still I love the smart code.
Like (Since someone always questions the reason for having gap-less sequences: it's required by law in some countries that there are no gaps in your invoice numbers)
Regards // Tomas
Like Indeed my is a web application, I am trying to understand how to adpat to my solution that Sp, but if I create a table with PK: InvoiceNr, Year, ID, the problem is not to get ID but next InvoiceNr (maybe with max InvoiceNr + 1) avoiding the concurrency in the case of two call in the same time. :S
Like First thanks for your reply, but unfortunately, I have inherited the DB design, this DB is designed to manage Invoice process, so in that case the ID need to reset each begin of the year.
Indeed the PK is Year, Id.
So you have any suggestion to manage the concurrence? :)
Like
Comments