Sql-server – ‘Id’ with the format: YYYYNNNNNN with the NNNNNN part restarting each year

sql servert-sql

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!

Best Answer

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) ;
Related Question