'Id' with the format: YYYYNNNNNN with the NNNNNN part restarting each year
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) ;
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 ?