0

I need to generate the progressive number of a invoices, avoiding gaps in the sequence:
At beginning I thought it was quite easy as

    SELECT MAX(Docnumber)+1 as NewDocNumber
    from InvoicesHeader

but since it takes some time to build the "insert into InvoiceHeader" query and another request could arrive, assigning to both Invoices the same NewDocNumber

I'm now thinking to avoid to generate the DocNumber in advanced and changed query to:

INSERT INTO InvoicesHeader  (InvoiceID,..., DocNumber,...) 
SELECT @InvoiceID,..., MAX(Docnumber)+1,... FROM InvoicesHeader

but although (it should) solve some problems, it is still thread unsafe and not suitable for race conditions:

adding TABLOCK or UPDLOCK, in this way:

BEGIN TRANSACTION  TR1
    INSERT INTO InvoicesHeader WITH (TABLOCK)  
        (InvoiceID,..., DocNumber,...) 
    SELECT @InvoiceID,..., MAX(Docnumber)+1,... FROM InvoicesHeader
COMMIT TRANSACTION  TR1

Will solve the issue?

Or better to use ISOLATION LEVEL, NEXT VALUE FOR or other solution?

Joe
  • 1,033
  • 1
  • 16
  • 39
  • You can use Scope_Identity() also https://learn.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-2017. – Suraj Kumar Mar 18 '19 at 11:38
  • But Scope_Identity() could generate gaps, while I have to absolutely avoid gaps – Joe Mar 18 '19 at 11:46
  • 1
    @Joe- You if you cannot have gaps you cannot have any sort of scalability. You will have to run your transactions one after the other. That may be ok, but generally is not. You might want to elaborate on this requirement as it's rarely true or, you can assign the unique, gap-free number after the event. – LoztInSpace Mar 18 '19 at 11:52
  • See the answer I gave to [this question](https://stackoverflow.com/q/35261411/243373). – TT. Mar 18 '19 at 12:02

1 Answers1

0

You already having thread safe generation of sequence generation in SQL Server. Read about Create Sequence. It is available starting from SQL Server 2012. It is better to use as sequence is generated outside the transaction scope.

Sequence numbers are generated outside the scope of the current transaction. They are consumed whether the transaction using the sequence number is committed or rolled back.

You can get next value from the sequence. We have been using sequences for generating order numbers and we have not found issues, when multiple order nubmers are generated in parallel. 

SELECT NEXT VALUE FOR DocumentSequenceNumber;

Updated, based on comments, if you have four different documenttypes, I would suggest you to first generate sequence and then concatenate with a specific document type. It will be easier for you to understand. At the end of the year, you can restart the sequence using ALTER SEQUENCE

RESTART [ WITH ] The next value that will be returned by the sequence object. If provided, the RESTART WITH value must be an integer that is less than or equal to the maximum and greater than or equal to the minimum value of the sequence object. If the WITH value is omitted, the sequence numbering restarts based on the original CREATE SEQUENCE options.

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
  • Actually I've not understood how `Next Value For` works: how can he check that the number has been used or not. But, I have 4 different DocTypes and Number has to be reset every year, therefore using sequence seems me not the best solution, since would require to create a bunch of sequences – Joe Mar 18 '19 at 11:58
  • Next value will be unique. You dont need to check whether number has been used or not. I have updated my answer. – Venkataraman R Mar 18 '19 at 12:30
  • Thanks for your time: A Unique Value is Important, but I need also to avoid gaps: from what I've understood NEXT VALUE FOR is a kind of NEWSEQUENTIALID but with custom pattern values, that in some condition can generate gaps, unless put everything into a Transaction.. Will work on this.. – Joe Mar 18 '19 at 12:44