11

I have a a business requirement that each record in the Invoice table has an id which looks like YYYYNNNNNN.

The NNNNNN part needs to restart at the beginning of each year. So the first row entered in 2016 would look like 2016000001 and the second like 2016000002 etc. Lets say the last record for 2016 was 2016123456, The next row (of 2017) should look like 2017000001

I don't need this id to be the primary key and I store the creation date as well. The idea is that this 'display id' is unique (so I can query by it) and human group-able, by year.

It is unlikely that any records would be deleted; however, I would be inclined to code defensively against something like that.

Is there any way I could create this id without having to query for the max id this year every time a insert a new row?

Ideas:

  • A CreateNewInvoiceSP, which gets the MAX value for that year (yucky)
  • Some magical built in feature for doing exactly this (I can dream right)
  • Being able to specify some UDF or something in the IDENTITY or DEFAULT declaration (??)
  • A view which uses PARTITION OVER + ROW() (deleted would be problematic)
  • A trigger on INSERT (would still need to run some MAX query :( )
  • An annual background job, updated a table with the MAX for each year inserted which I then... Something?!

All of which are a bit non ideal. Any ideas or variations welcome though!

  • You have some good answers but if you have year, id as a PK then select max is pretty fast. – paparazzo Apr 12 '16 at 23:32
  • using a select max id query is a common practice. use that. – Uğur Gümüşhan Apr 29 '16 at 2:18
17

There are are 2 elements to your field

  • Year
  • An auto incrementing number

They do not need to be stored as one field

Example:

  • A year column which has a default of YEAR(GETDATE())
  • A number column based on a sequence.

Then create a computed column concatenating them (with appropriate formatting). The sequence can be reset on change of year.

Sample code in SQLfiddle:*(SQLfiddle doesn't always work)

-- Create a sequence
CREATE SEQUENCE CountBy1
    START WITH 1
    INCREMENT BY 1 ;

-- Create a table
CREATE TABLE Orders
    (Yearly int NOT NULL DEFAULT (YEAR(GETDATE())),
    OrderID int NOT NULL DEFAULT (NEXT VALUE FOR CountBy1),
    Name varchar(20) NOT NULL,
    Qty int NOT NULL,
    -- computed column
    BusinessOrderID AS RIGHT('000' + CAST(Yearly AS VARCHAR(4)), 4)
                     + RIGHT('00000' + CAST(OrderID AS VARCHAR(6)), 6),
    PRIMARY KEY (Yearly, OrderID)
    ) ;


-- Insert two records for 2015
INSERT INTO Orders (Yearly, Name, Qty)
    VALUES
     (2015, 'Tire', 7),
     (2015, 'Seat', 8) ;


-- Restart the sequence (Add this also to an annual recurring 'Server Agent' Job)
ALTER SEQUENCE CountBy1
    RESTART WITH 1 ;

-- Insert three records, this year.
INSERT INTO Orders (Name, Qty)
    VALUES
     ('Tire', 2),
     ('Seat', 1),
     ('Brake', 1) ;
| improve this answer | |
  • 1
    Maybe it's cleaner to have one sequence per year. That way there is no need to execute DDL as part of regular operations. – usr Apr 12 '16 at 15:01
  • @gbn So I would I need a background job to restart the SEQUENCE at the beginning of each year? – DarcyThomas Apr 12 '16 at 20:45
  • @usr Sadly you can't use NEXT VALUE FOR in a CASE statement (I tried) – DarcyThomas Apr 12 '16 at 22:50
8

Did you consider to create an identity field with seed = 2016000000?

 create table Table1 (
   id bigint identity(2016000000,1),
   field1 varchar(20)...
)

This seed should be autoincremented each year, for example at the night of 2017/1/1 you need to schedule

DBCC CHECKIDENT (Table1, RESEED, 2017000000)

But I already see problems with the design, for example: what if you have million records ?

| improve this answer | |
  • 2
    Another problem is if the records don't appear chronologically. Identity is probably not the way to go if this is the case. – Daniel Hutmacher Apr 12 '16 at 6:47
  • @LiyaTansky In my case I have been told the should only be 50k records per year. But I get what you mean about it being brittle with 1kk rows – DarcyThomas Apr 12 '16 at 20:48
1

What I did in this scenario was to multiply the year by 10^6 and add the sequence value to that. This has the advantage of not requiring a computed field with its (small) ongoing overhead and the field can be used as a PRIMARY KEY.

There are two possible gotchas:

  • make sure that your multiplier is sufficiently large so as never to be exhausted, and

  • you are not guaranteed a sequence without gaps due to the caching of the sequence.

I'm not an expert on SQL Server, but you can probably set an event to trigger at 201x 00:00:00 to reset your sequence to zero. That's also what I did on Firebird (or was it Interbase?).

| improve this answer | |
1

Edit: This solution does not work under load

I'm not a fan of triggers, but this seems best I could work out.

Pros:

  • No background jobs
  • Can make fast queries on the DisplayId
  • The trigger does not need to scan for the previous NNNNNN part
  • Will restart the NNNNN part every year
  • Will work if there is more than 100000 rows per year
  • Does not require schema updates (e.g., sequence resets) to keep working in the future

Edit: Cons:

  • Will fail under load (back to the drawing board)

(Credit to @gbn as I took some inspiration from their answer) (Any feed back & pointing out the obvious mistakes welcome :)

Add some new COLUMNs and an INDEX

ALTER TABLE dbo.Invoices
ADD     [NNNNNNId]      INT  NULL 

ALTER TABLE dbo.Invoices
ADD [Year]              int NOT NULL DEFAULT (YEAR(GETDATE()))

ALTER TABLE dbo.Invoices
ADD [DisplayId]     AS  'INV' +
                        CAST([Year] AS VARCHAR(4))+
                        RIGHT('00000' + CAST([NNNNNNId] AS VARCHAR(4)),  IIF (5  >= LEN([NNNNNNId]), 5, LEN([NNNNNNId])) )                  

EXEC('CREATE NONCLUSTERED INDEX IX_Invoices_DisplayId
ON dbo.Invoices (DisplayId)')

Add the new TRIGGER

CREATE TRIGGER Invoices_DisplayId
ON dbo.Invoices
  AFTER  INSERT
AS 
BEGIN

SET NOCOUNT ON;    

UPDATE dbo.Invoices
SET NNNNNNId = CalcDisplayId
FROM (SELECT I.ID, IIF (Previous.Year = I.Year , (ISNULL(Previous.NNNNNNId,0) + 1), 1) AS CalcDisplayId  FROM
        (SELECT 
            ID  
           ,NNNNNNId 
           ,[year]
        FROM  dbo.Invoices
        ) AS Previous
    JOIN inserted AS I 
    ON Previous.Id = (I.Id -1) 
    ) X
WHERE 
   X.Id = dbo.Invoices.ID       
END
GO
| improve this answer | |
  • I highly recommend not doing this. It's likely to deadlock and cause insert failures once you get under light load. Have you put a copy into a dummy database and hammered it with a few dozen threads at once doing inserts (and maybe selects/updates/deletes as well) to see what happens? – Cody Konior Apr 19 '16 at 2:42
  • @CodyKonior is it fundamentally flawed or could it be resurrected with a bit of judicious locking? If not how would you approach the problem? – DarcyThomas Apr 19 '16 at 2:49
  • Hmmm. Ran with 10 threads. Not sure if it is dead locks, but I do get some race conditions. Where one trigger completes, before the previous rows trigger has finished. This leads to a bunch of NULL values being entered. Back to the drawing board... – DarcyThomas Apr 19 '16 at 3:46
  • Disaster averted then :-) My secret is that I recognised the pattern for something I did about five years ago. I only know that the way you scan the table inside the trigger looking for the next sequence trips things up under load. I don't remember how I resolved it but I can check later. – Cody Konior Apr 19 '16 at 3:54
  • @CodyKonior I don't think it is doing a scan (ON Previous.Id = (I.Id -1) should just seek), but yeah still doesnt work. If I could lock the table(?) during the insert and trigger then I think it would work. But that sounds like a code smell as well. – DarcyThomas Apr 19 '16 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.