Contact Us

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.

-- =============================================
-- Author:		Doron Farber
-- Create date: 02/16/2014
-- Description:	Generate Invoice Number in SQL 
-- =============================================
ALTER PROCEDURE [dbo].[GetInvopiceNumber] 
AS
BEGIN
	SET NOCOUNT ON;
  
	INSERT INTO [dbo].[InvoicesNumbers]
           ([NewInvoiceNo])
	VALUES
	       (1)
	SELECT SCOPE_IDENTITY() AS InvoiceID 
END

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.

Go Back to Our: Main Blog

Written by: Doron Farber - The Farber Consulting Group, Inc

There are 15 comments for "Create Unique Invoice Number in SQL"

avatar
By Edhy Rijo | February 17,2014

Hi Doron,

Excellent article, thanks for keeping up your blog series on MS SQL server.

I have to admit, I am using MAX() for some sequential numbers which fall into the category you describe of 5 to 15 users and have never had any issues on getting duplicate numbers because since I create the numbers from my applications, I have code in place to make sure the number is valid, but like anything else, it may have some un-noticiable overhead.

But what I like the most is the new "Create Sequence" command in SQL2012, that feature is just great and easy to implement.

Reply
avatar
By Addmore | February 25,2014

I like it very much and it is a great idea.

Reply
avatar
By Sotiris Filiippidis | February 25,2014

We did a similar thing at a company I worked at a few years ago with SQL Server 2005 (A separate table). The only difference is that we had only one record and wrapped the whole thing inside a transaction since we had to read the previous value, increment it and write it back and we wouldn't want any other user trying to read or write at the same time.

It wasn't invoice numbers, but we still needed a consistent numbering without gaps - we didn't need to log anything but the last number used.

In my opinion, the approach you are describing is an excellent one, way better than what we did back then. :)

Reply
avatar
By Yaroslav Kazmin | February 25,2014

Great post, I certainly will utilize this approach in my projects.

Reply
avatar
By Jeff | January 01,0001

Hi Doron,

I will definitely will ask my developer to use this great sample of code to generate an invoice number for my applications. I see that you provide Remote DBA services and will contact soon to help us to manage our database.

Thanks,

Jeff

Reply
avatar
By Vaughn Seward | February 25,2014

Could be very useful. I like that we'll be able to use Sequences in SQL-Server 2012.

Thanks, Vaughn

Reply
avatar
By Vincenzo | May 31,2014

The question is...

If you don't have an Identity column? :(

Reply
avatar
By | January 01,0001

Hi Vincenzo,

That it is a bad design of the database. I would not create any table with no identity field.

Regards,

Doron

Reply
avatar
By Vincenzo | May 31,2014

Hi Doron,

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? :)

Reply
avatar
By | May 31,2014

Hello Vincenzo,

The above SP is a stand alone program that could be called when ever a new invoice is created. So you can combine that code in your SP and always grab the new Invoice ID. That InvoicesNumbers table always will store PKs for the invoices.

Regards,

Doron

Reply
avatar
By Vincenzo | May 31,2014

HI Doron,

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

Reply
avatar
By | May 31,2014

Hi Vincenzo,

You really need to read carefully my article. I addressed this issue and no matter how many hits you get at the same time you will always get a unique PK. You can use the above code safely.

Regards,

Doron

Reply
avatar
By Tomas | October 22,2014

This might be a stupid question, but doesn't identity values grow even if the insert fails/is rolled back? Which in turn will make gaps in your number sequence? Also, what is the point in storing the invoice numbers in a separate table, is not not better to keep those together with your invoice data?

(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

Reply
avatar
By | May 30,2015

Hi Thomas,

That code never failed on us.

Regards,

Doron

Reply
avatar
By josimar | May 30,2015

Is it work for SQL Azure?

Reply

Add your comment