CODEWITHSUNDEEP
c#sqlsql-serverazure-sql-database
Julian50
Julian50

Reputation: 2602

SQL Numeric Column forbid Gaps

I'm doing invoice table in my database. I need a integer column to store invoice number. This column has to forbid gap between number. Identity doesn't work because on rollback can produce a gap.

So what I want is:

InvoiceId(Primary key identity)     InvoiceNumber(Unique, NOT NUll)
      1                                      1
      2                                      2
      10                                     3
      13                                     4

Is there a special way in sql to do this ? If there is no solution in sql, how should I do it in c# + entity?

EDIT 1: additionnal information: A row will be never deleted.

EDIT 2: Why I need gapLess Column: it's a law requirement (french law). Invoice number as to be gapLess, to show that you didn't removed invoice.

Upvotes: 1

Views: 133

Answers (2)

nvoigt
nvoigt

Reputation: 77304

There is no way to protect from gaps if you access your database in parallel.

Simple case:

  1. Process A) creates an invoice. (#1)
  2. Process B) creates an invoice. (#2)
  3. Process A) rolls back it's transaction.
  4. Gap.

You could either lock your whole table for the whole transaction. That means that only one of your processes can create an invoice at the same time. That might be acceptable for small companies where one person creates all invoices by hand.

Or you could leave the column empty and once per night you lock the whole table and write those numbers that are not set yet. That means you get the invoice number later in your invoice process, but it's without gaps.

Or you could read the requirements again. Germany had something equally stupid, but it was only meant to have those numbers on the form for the tax department. So you could use your normal invoice numbers with gaps and when sending them to this bureaucratic monstrosity, you would generate a unique, gap free number on export only.

Upvotes: 3

Vladimir Mezentsev
Vladimir Mezentsev

Reputation: 918

Because there are multiple users you can't recalculate value at client side. Just create triggers in your database for insert/delete that will recalculate the InvoiceNumber for the entire table.

Upvotes: 0

Related Questions