How to implement invoice number functionality?

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.

Who is Participating?
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.
Dave BaldwinFixer of ProblemsCommented:
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.
Éric MoreauSenior .Net ConsultantCommented:
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.
AndyAinscowFreelance programmer / ConsultantCommented:
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.
Ricky66Author Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.