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.
