Year Wise Separate Sequences in SQL server

Many enterprise systems like Invoice, Order Management and Banking need numbering like 2024-0001, 2024-0002 and next year it should restart from 2025-0001. SQL Server does not do this automatically, so we need to design it correctly. There is no any function in SQL server to generate such kind of number.

For the solution first we need to create a sequence, also we will create a SQL Server job which ALTER the SEQUENCE and RESTART WITH 1 every starting year.

Basically, to generate the column we are adding year and the sequence number.

Let’s see the demo.

Creating a sequence

CREATE SEQUENCE Seq_YearWiseOrder START WITH 1 INCREMENT BY 1;

Now creating table.

CREATE TABLE orders_yearwise   (      orderid         INT IDENTITY(1, 1) PRIMARY KEY,      orderyear       INT NOT NULL DEFAULT Year(Getdate()),      yearordernumber VARCHAR(20) NOT NULL DEFAULT Concat(Cast(Year(Getdate()) AS VARCHAR      (20))      , '-', RIGHT('0000'      +Cast( next value FOR seq_yearwiseorder AS VARCHAR(20)), 4)),      customername    VARCHAR(100),      createddate     DATETIME DEFAULT Getdate()   );

Now inserting records into this table.

insert into orders_yearwise(customername) values ('Rajesh')   insert into orders_yearwise(customername) values ('Bagesh')

Record inserted successfully. See the records

Important Note:

Every year we must need to alter the sequence and reset it.

ALTER SEQUENCE Seq_YearWiseOrder RESTART WITH 1;

 

Let’s see Today is 12/22/2025

The sequence will reset on mid night of 01/01/2026 and it will again start from 1 sow our sequence will be like 2026-0001, 2026-0002.

See today is 1/1/2026. Our SQL Job is already run and reset to 1.

Now inserting the records.

See its generated by new number and year has been changed.

If we want to change only year number then no need to reset the Sequence every year.