How to implement invoice number functionality?

Posted on 2012-08-29
Medium Priority
Last Modified: 2012-09-04
Environment: C#.Net, Sql Server

I need to create an invoice number in an application which is auto generated for each account.

Below are the 2 options I know. Please advise what would be the best approach to implement this functionality.

1). Identity column
2). Create a table in SQL Server that has just 1 column and 1 row. and insert number 1 in there. Now everytime, I want to create an invoice number I can read the value from this counter table and add 1.

Question by:Ricky66
LVL 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 400 total points
ID: 38346581
In general, an Identity column should not be used for anything else.  Your second idea is good.  However, you should probably have a column in your main invoice table that tells whether a particular row is still valid.  The purpose of that is so you don't have to delete any rows and invoice numbers and you could ORDER BY that column and figure out what the next invoice number should be.
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 400 total points
ID: 38346598
if you are using SQL 2012, you can use the new Sequence feature: http://emoreau.com/Entries/Articles/2012/05/SQL-Server-2012--Whats-new-for-the-developers.aspx

about your 2 options, I would never add 1 to a saved counter. if 2 users are doing the same operation at almost the same time, you can get twice the same value.
LVL 18

Accepted Solution

vasto earned 1200 total points
ID: 38346754
A separate table with counters will work fine if you always use a transaction when you increment the number and the transaction isolation level is serializable . This will guarantee you that nobody will be able to read the current invoice number if somebody is in a process of changing it. However this might lead to timeouts if you have many simultaneous requests for a new invoice number and invoice saving operation takes too much time.

I guess the steps should be :
  - prepare the invoice data
  - open a transaction
  - get a new invoice number
  - save the invoice
  -commit or rollback the transaction

Identity column will be easier to use , but you will have missing numbers when a transaction fails.
LVL 45

Expert Comment

ID: 38346817
Both options you say are fairly poor - for the reasons already stated.

I note you also haven't actually told us what format the invoice number is or what requirements it has.

Author Closing Comment

ID: 38365994

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question