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.