How to implement invoice number functionality?

Posted on 2012-08-29
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 82

    Assisted Solution

    by:Dave Baldwin
    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 69

    Assisted Solution

    by:Éric Moreau
    if you are using SQL 2012, you can use the new Sequence feature:

    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

    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 43

    Expert Comment

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    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…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now