2

I try to create and print invoices using web forms. I am trying to find the best recommended practice that I should use. Until now the practices I have are;

  1. Create a table that will store "next invoice number" and then generate the next number using a stored procedure, something like "next number" + 1.

  2. Store the previous number in the invoice DB table and then in my c# code increment it by one.

What I would like to avoid is use the SQL Server identity (auto increment) to generate the invoice number because of the gaps that may be happen (i have see in real life to occur ) but there are also articles that mention about these gaps. Why are there gaps in my IDENTITY column values? Identity increment is jumping in SQL Server database

My question is what is the recommended practice that I should follow to ensure that every invoice will get sequential unique invoice number?

Alex
  • 4,885
  • 3
  • 19
  • 39
focus
  • 171
  • 9
  • 31
  • Just to clear out any [XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) issues, what do you want to achieve by using sequential invoice numbers ? I would actually say that using sequential invoice numbers is not a good practice in itself; if these numbers are shown externally then any of your customers can know how many invoices you produce. Or maybe you actually want sequential numbers *per customer* ? – Pac0 Nov 09 '17 at 08:47
  • What is wrong with the approach "a)"? Have you encountered problems with it? The "modern" implementation of approach "a)" would use [`SEQUENCE`](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql) but I would not bother rewriting existing code. – Alex Nov 09 '17 at 08:48
  • Pac0,There are legislation issues that obligate a system to produce sequential invoice numbers. Also i am not asking for code... I asking about the recommended approach. I also believe that practice a) is better – focus Nov 09 '17 at 08:50
  • 1
    You cannot 100% reliably prevent gaps if you create your invoices in parallel (which I assume is the case). You can of course lock whole table while inserting invoice but I suppose that's not good solution for you (on the other hand if you have small load and don't create invoices too often - why not). If that "no gaps" requirement is not super strict requirement (like some law in your country) - just forget about it. – Evk Nov 09 '17 at 08:51
  • @Pac0, I don't see an XY Problem here. OP is showing us that he has done his homework by listing methods he has found, which is considered a good practise on SO, right? – Alex Nov 09 '17 at 08:52
  • @Pac0 it happens quite often that some law requires invoice numbers to be strictly sequential (and after digging to figure out why - it might even make sense). – Evk Nov 09 '17 at 08:55
  • 1
    @Alex I was not challenging the fact that he did research on his actual question. He did, and the question is good. I just wanted to make sure that his underlying problem he was trying to solve actually required the use of sequential numbers. OP has clarified that (legal requirement), so it is definitely not a XY problem. – Pac0 Nov 09 '17 at 09:07
  • @focus, how many parallel invoice sequences are we talking about? To elaborate: you have e.g. 100 customers where **each individual** customer has Invoices going 1, 2, 3 or do you have one sequence spanning all customers? – Alex Nov 09 '17 at 10:10
  • @Alex, i have one sequence spanning all customers – focus Nov 09 '17 at 10:22
  • @focus, does it matter in which order Invoice numbers are assigned? To elaborate: Inv A is created at 21:56 (hh:mm) and InvB is created at 22:00; Is it critical that Inv A gets a smaller invoice number? An idea (just an idea) is to add invoice records without a number and then have a job run at certain intervals to populate these numbers. It will have some delay in generating numbers in terms of user experience but IMO it would be quite resilient in terms of gaps etc. – Alex Nov 09 '17 at 10:36

1 Answers1

1

I would recommend approach "a)".

There are two variants to it:

  1. Use SEQUENCE. Make sure that NO CACHE option is specified.
  2. As you described, create a "Counter" table that will store next ID to be assigned.

The code for it would look something like this:

CREATE TABLE Counter( NextID INT NOT NULL DEFAULT(1))
INSERT INTO Counter VALUES( DEFAULT )

DECLARE @NextID INT
UPDATE Counter
SET @NextID = NextID,
    NextID = NextID + 1

SELECT @NextID

Note: I don't fully understand your description of option "b)". Assuming that you mean to say: that you want to search current invoice table to find MAX existing invoice and then add one to it then I would strongly advise against this approach as it is fragile. If you do not create a covering index for your search query than you may end with problems due to race condition.

Alex
  • 4,885
  • 3
  • 19
  • 39
  • Thank you for your answer. My question is: is there any possibility to have gaps in a Sequence? Do you believe if i dont have cache it will eliminate the gaps? I am asking because in this post i see in the answer that it may be a possibility to have gaps(because of cache...). https://stackoverflow.com/questions/16472431/sql-server-sequence-gaps – focus Nov 09 '17 at 09:50
  • 1
    @focus there can be gaps still, every time transaction fails. You grab value X, trying to insert invoice with that value, transaction fails for whatever reason - now you have a gap because X will not be used any more. – Evk Nov 09 '17 at 10:02
  • 1
    The only obvious situation where gaps may be created is when a transaction fails and is rolled back. The only way I can see that you can avoid this is by using serialisable transactions with "Counter" table. Note: it will introduce a lot of locking and blocking and will hurt performance. You would still have to do a lot of extensive testing (including pulling the power cord from computer) to confirm that it works in all cases. – Alex Nov 09 '17 at 10:05