CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Crudler
Crudler

Reputation: 2286

Sql server dynamic sequences

Here is a theoretical scenario,

Suppose I have a client table and an invoice table. 1 client can have many invoices.

Now I want each invoice to have an invoice number that is unique to that client

i.e.

ClientId InvoiceNo
1        IN0001
2        IN0001
2        IN0002
2        IN0003
3        IN0001

Currently I am controlling this in my application by looking at max values etc but this is obviously not a great solution. I would much rather get my database to do this for me, as it should remove the risk of creating duplicate invoice numbers for a single client (race condition?)

I have been reading up on Sql Server 2012's SEQUENCE which sounds great, but the problem is that I would still need a seperate sequence per client

i.e. 

CREATE SEQUENCE InvoiceNum_Client1.....
CREATE SEQUENCE InvoiceNum_Client2.....
CREATE SEQUENCE InvoiceNum_Client3.....

but something feels very dirty about making db meta changes from my app everytime a new client is created. Also then my trigger would have to do something like this (which I wouldn't even begin to know how to do)

NEXT VALUE FOR InvoiceNum_Client + @ClientId

etc

So my next thought was to have a "sequence" table, i.e.

ClientID   INSequenceNumber
1           1
2           3
3           3

And in my trigger grab the InSequenceNumber for a given client, use it to make my invoiceNumber, and then update the sequence table, incrementing InSequenceNumber by 1 for the same client. It should have the same effect, but I am just not sure about the inner workings of transactions and scope etc

So my questions are

  1. Is there any big disadvantage to my self rolled sequence method?
  2. Is there another solution that I am perhaps overlooking?

Thanks!

Upvotes: 3

Views: 2626

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Why do the clients have to have their own sequences? Have a global sequence number. Then, if you want to get the client sequences in order, use row_number():

select i.*, row_number() over (partition by clientid order by invoiceno) as ClientInvoiceSequence
from invoices;

Note: you might want the order by to be by another field such as date.

If you start storing this information in the database, you will need to do a lot of bookkeeping and careful transaction management:

  • What happens when two invoices are entered "at the same time"?
  • What happens when an invoice is deleted?
  • What happens when an invoice is modified in such a way that it might change the sequence?

You are much better off using an identity column and calculating the sequence when you need it.

Upvotes: 1

Dan
Dan

Reputation: 10680

Is there a specific reason for requiring that the invoice numbers are only unique per client? In most ERP systems, invoice numbers are typically globally unique, making implementation much easier. No matter what, you should have an Invoice table that contains a primary key (and you shouldn't use composite primary keys - that's just downright bad data modelling).

This leaves us with a scenario where you might not need to store the "per-client-invoice-number" in the database at all. Assuming you have a table called "Invoices" containing the following data:

Id   | ClientId
---------------
  1  |        1
  2  |        1
  3  |        2
  4  |        1
  5  |        3
  6  |        2

Here, Id is the Primary Key of the Invoices table, and ClientId is a foreign key. A query like this:

SELECT 
    ClientId,
    'IN' + RIGHT('0000' + 
             CONVERT(VARCHAR, ROW_NUMBER() OVER (PARTITION BY ClientId 
                                                 ORDER BY Id)) AS InvoiceNo,
    Id
FROM Invoices
ORDER BY ClientId, InvoiceNo

Would return:

ClientId | InvoiceNo | Id
---------------------------
      1  |    IN0001 |  1
      1  |    IN0002 |  2
      1  |    IN0003 |  4
      2  |    IN0001 |  3
      2  |    IN0002 |  6
      3  |    IN0001 |  5

Upvotes: 1

Related Questions