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.

Create multiple auto generated column in SQL server table

We can have multiple auto-generated columns in a SQL Server table, and this is very common in real-time enterprise systems like billing, insurance, logistics, banking, ecommerce, etc.

There is multiple way to generate multiple auto-generated ids.

Ø  Identity and Sequence

Ø  Multiple Sequence

Ø  Auto GUID and Identity

Ø  Computed Column

Ø  Default columns

we can only one identify key will create in a table. Identity key is a auto generated key. If we create multiple it will throw error.

create table demotbl (id int identity(1,1), insert_id int identity(1,1) )

 It will throw an error.

Here we are creating a table with all above combination.

Creating sequence

CREATE SEQUENCE Multi_Auto_Seq     AS BIGINT     START WITH 100000     INCREMENT BY 1     CACHE 50;

Now creating table

CREATE TABLE multi_auto_demo   (      localorderid INT IDENTITY(1, 1) PRIMARY KEY,-- Auto 1      orderid      BIGINT DEFAULT next value FOR multi_auto_seq,-- Auto 2      localtrackingnumber AS ( 'LTRK-'           + Cast(localorderid AS VARCHAR(20)) ),      --Auto 3 -complted column from identity comuln      trackingnumber AS ( 'TRK-' + Cast(orderid AS VARCHAR(20)) ),      -- Auto 4 --complted column from Sequence      customername VARCHAR(100),      amount       DECIMAL(12, 2),      createddate  DATETIME DEFAULT Getdate(),-- Auto 5      updateddate  DATETIME DEFAULT Getdate(),-- Auto 6      uniqueref    UNIQUEIDENTIFIER DEFAULT Newid() -- Auto 7   );

Table created successfully.

Now inserting few records.

INSERT INTO multi_auto_demo (CustomerName, Amount) VALUES ('Bagesh', 55000.75),        ('Rajesh', 43000.25),        ('Mahesh', 32000.00);

Record inserted successfully.

Now see the data

In this way we can created multiple auto generated column in a table.

Sequence work with MERGE operation in SQL server

Yes, SQL Server SEQUENCE works perfectly with MERGE. When MERGE executes the WHEN NOT MATCHED THEN INSERT clause, each inserted row gets a value from NEXT VALUE FOR. Sequence guarantees uniqueness across multiple inserted rows, but it is not transactional, so gaps may occur if MERGE rolls back or server crashes. Using DEFAULT with sequence is recommended for cleaner design.

Let’s see the demo

--creating a sequence CREATE SEQUENCE Merge_Seq_Demo START WITH 1 INCREMENT BY 1;

Now creating a table

CREATE TABLE Merge_tbl_Demo (     CustomerID BIGINT DEFAULT NEXT VALUE FOR Merge_Seq_Demo,     CustomerName VARCHAR(50) );

Now creating stage table.

CREATE TABLE stgTbl (     CustomerName VARCHAR(50) );  INSERT INTO stgTbl VALUES ('Bagesh'),('Amit'),('Rahul');

Now we are merge statement to insert and update the records.

MERGE Merge_tbl_Demo AS T USING stgTbl AS S ON T.CustomerName = S.CustomerName WHEN NOT MATCHED THEN    INSERT (CustomerName)    VALUES (S.CustomerName);

Records inserted. See the records

Either we can use by default constrain or we can provide run time sequence number when we are inserting the records.

Can we insert value on the sequence column

Yes, we can manually insert values into a column that uses SEQUENCE. Unlike IDENTITY, SEQUENCE is only a value provider through NEXT VALUE FOR, not a hard-bound generator.

We can override and insert our own values without enabling IDENTITY_INSERT. However, manually inserting values does not change the internal state of the sequence and may create gaps or duplicates if primary key rules are not handled properly.

Let’s see the demo

CREATE SEQUENCE Seq_Insert_demo START WITH 1 INCREMENT BY 1;

Now creating a table in that table, we are using this sequence as default.

create table Insert_Demo ( ID bigint default next value for Seq_Insert_demo, Nm varchar(50) )

Inserting few records.

insert into Insert_Demo(Nm) values ('Bagesh') insert into Insert_Demo(Nm) values ('Rajesh')

See the data

Now we are inserting id value manually. I mean overwriting the default value.

insert into Insert_Demo(id,Nm) values (999,'Bagesh') insert into Insert_Demo(id,Nm) values (1000,'Rajesh') select * from Insert_Demo

 

Again, if we insert the default value it will start form the next sequence.

Because SEQUENCE is not bound to the column like IDENTITY. It just provides values, but SQL Server does NOT restrict us from inserting custom values. If we want to make it unique, we need to create primary key or unique key.

Reset SEQUENCE in SQL Server

Unlike IDENTITY, SEQUENCE can be safely and easily reset without dropping or recreating. Yes, SQL Server SEQUENCE supports restart using ALTER SEQUENCE RESTART WITH. Unlike IDENTITY, SEQUENCE restart is clean, safe, and metadata-based. TRUNCATE, DELETE, ROLLBACK do NOT reset sequence. Reset is often used in year-wise numbering, testing, and controlled business environments. However, we must ensure business does not rely on previous numbers to avoid conflicts.

In one word, TRUNCATE does NOT reset SEQUENCE. DELETE does NOT reset SEQUENCE
ROLLBACK does NOT reset SEQUENCE Only ALTER SEQUENCE can reset it.

Why we need to reset the sequence. Below few are example where we need to reset

Ø  Financial year reset (Start numbering from 1 every year)

Ø  Testing / QA reinitialization

Ø  Business requirement to restart from a specific number

Ø  Accidentally generated large jump

Ø  Client migration and need continuity from previous system number

Ø  Demo environments

Let’s see the demo

Creating a sequence

CREATE SEQUENCE Seq_Reset_demo START WITH 1 INCREMENT BY 1;

Sequence created successfully.

Creating a table and we are using this sequence in this table as default column.

create table Reset_Demo ( ID bigint default next value for Seq_Reset_demo, Nm varchar(50) )

Now inserting few records.

insert into Reset_Demo(Nm) values ('Bagesh') insert into Reset_Demo(Nm) values ('Rajesh') insert into Reset_Demo(Nm) values ('Amit') insert into Reset_Demo(Nm) values ('Ramesh')

Let’s see the data.

If we truncate or delete the records id will not reset. See below.

truncate table Reset_Demo insert into Reset_Demo(Nm) values ('Bagesh') select * from Reset_Demo

Id is not reset

If we want to reset the value we need to alter this sequence.

ALTER SEQUENCE Seq_Reset_demo RESTART WITH 1;

Now ID is reset from 1.

In fact, if we want to start from specific number we can do it.

ALTER SEQUENCE Seq_Reset_demo RESTART WITH 1000;

Next number will start from 1000 onwards. Let’s see the example.

When we are resetting the sequence, it will not impact on the table structure.

What Happens When Multiple Sessions Call NEXT VALUE FOR SEQUENCE

When multiple sessions request sequence values simultaneously, SQL Server allocates unique increasing numbers safely and efficiently. Values are assigned immediately without waiting for commit, so gaps may occur. Order between sessions isn’t guaranteed, but duplication never happens. CACHE improves performance but may increase gaps.

SQL Server SEQUENCE guarantees that Every session gets a unique value No duplicates ever. Sequence values are allocated atomically. Allocation is thread-safe & highly concurrent SQL Server internally serializes sequence generation so parallel sessions never clash. Sequence does NOT wait for commit Values are assigned immediately. If a transaction rolls back then number is not returned. Order is not guaranteed across sessions Even though numbers are increasing, concurrency scheduling means that session 2 might receive a number before Session 1 commits.

When we enable CACHE then numbers are pre allocated and it will be Super-fast under concurrency but if SQL crashes, cached numbers can be lost (gaps). When we are not enabling CACHE its Strict persistence and slower than enable CACHE.

Let’s see the demo

Here creating a sequence

CREATE SEQUENCE Seq_Demo_HC AS BIGINT START WITH 1 INCREMENT BY 1 CACHE 50;

Now we are using this Sequence in multiple high transaction.

We have run this on several window.

--window 1 BEGIN TRAN; SELECT NEXT VALUE FOR Seq_Demo_HC AS Seq_Demo_HC; -- Do NOT commit yet

2^nd window

3^rd window

And so on.

All got unique IDs even before commit.

Effect of TRUNCATE on SEQUENCE

TRUNCATE does not reset SEQUENCE because SEQUENCE is not stored with the table. It is a standalone object and maintains its last generated value separately. After truncate, the same sequence continues its next number. If we want to reset, we must use ALTER SEQUENCE RESTART. If we are using identity then it will reset the identity.

Let’s see the demo

Creating a sequence.

CREATE SEQUENCE Trunc_Sequence_demo AS INT START WITH 1 INCREMENT BY 1;

Now creating a table and using this sequence as autogenerated id.

create table trunc_demo ( id int default next value for Trunc_Sequence_demo, nm varchar(50) )

Table created successfully. Now inserting few records.

insert into trunc_demo(nm) values ('Bagesh') insert into trunc_demo(nm) values ('Ramesh') insert into trunc_demo(nm) values ('Suresh') insert into trunc_demo(nm) values ('Mahesh')

Records inserted successfully.

See the record from this table.

Now truncating this table.

Table truncated and there is not data into this table.

Now inserting few records. Last ID was 4 so next ID will start from 5.

ID is not reset to 1. Even we delete the delete the all records the ID will start from the next only. Let’s delete these records.

Now we are inserting records and see the ID. It will start from 7.

Instead of using sequence if we use identity it will reset it.

Can Sequence generate descending values

Yes, SQL Server SEQUENCE supports descending numbering. We can simply use a negative INCREMENT value. For example, INCREMENT BY -1 will generate values in reverse order. Sequence can even start from MAX value and go down to MIN value. This is not possible with IDENTITY.

See the demo

CREATE SEQUENCE Descending_Sequence_demo AS INT START WITH 1000 INCREMENT BY -1 MINVALUE 1;

Here sequence is start 1000 and decremented by 1.

See below

Can We Share same sequence between multiple tables

Yes, SQL Server SEQUENCE can be shared across multiple tables. It is a separate database object and not table dependent like IDENTITY. Multiple tables can generate values using NEXT VALUE FOR. It ensures globally unique numbering, supports caching, works in distributed systems, and is ideal for enterprise microservice architecture. However, sequence is not transactional and gaps may occur, which is acceptable in real enterprise systems.

See the demo

Creating a sequence

CREATE SEQUENCE Globle_Seq_Demo     AS BIGINT     START WITH 500000     INCREMENT BY 1;

Now creating tables.

create table Globle_Seq_tbl1 ( id bigint , Nm varchar(50) )  create table Globle_Seq_tbl2 ( id bigint , Nm varchar(50) )

 Now inserting records in both tables using the same sequence object.

See the records in both tables.

So, we can use sequence in the multiple tables.

How SQL Server SEQUENCE works together with the SAGA pattern in microservices

In a monolithic application, everything happens inside a single database transaction. But in microservices, one business transaction is broken into multiple services. For example, when we place an order in Amazon or any e-commerce site Order Service creates order, Payment Service charges, Inventory Service reserves stock, Shipping Service prepares delivery and Notification Service sends updates. All are different microservices with different databases. There is no single ACID transaction across them.

So how do we guarantee:

Ø  Reliability

Ø  Failure handling

Ø  Rollback behavior

Ø  Traceability

This is where SAGA Pattern comes in.

Saga simply means is Instead of one big transaction, we break the workflow into multiple local transactions and if something fails, we run compensation steps.

Architectural diagram of SAGA design pattern

SAGA works on two approaches

Orchestration Saga

It is a microservices design pattern where a central coordinator (orchestrator) manages a distributed transaction by commanding participating services, ensuring data consistency through a sequence of local transactions and triggering compensating actions if any step fails, offering centralized control but creating a potential single point of failure.

For example,

it calls Payment,
then Inventory,
then shipping
and controls the full workflow.

Choreography Saga

It is a design approach in microservices for managing distributed transactions, where services coordinate by publishing and subscribing to events without a central controller, ensuring data consistency through a series of local transactions and compensating actions for failures, ideal for simpler workflows with few participants.

For example,

No central controller.

Everything runs using events like Kafka / RabbitMQ.
OrderCreated event → PaymentService
PaymentDone event → InventoryService
StockReserved event → ShippingService

If anything fails, compensating events happen, like refund payment or restock inventory.

Let’s see the demo

we need a single Order ID right from the beginning. This Order ID must travel across:
Payment logs, Inventory records, Shipping service, Notifications, Audit trails, Retry events.

If we use IDENTITY, it will not work because it is generated after insert. Here we want ID before inserting the records. SQL Server SEQUENCE allows us to generate numbers before inserting data. It means We can generate Order ID first, use it everywhere, insert later. Sequence is perfect for microservices. Perfect for messaging systems. Perfect for Saga.

 Creating sequence

CREATE SEQUENCE SAGA_Demo_Seq     AS BIGINT     START WITH 500000     INCREMENT BY 1;

Sequence created successfully. Now creating few tables.

CREATE TABLE orders   (      orderid      BIGINT PRIMARY KEY,      customername VARCHAR(100),      amount       DECIMAL(10, 2),      status_cd    VARCHAR(50)   );  CREATE TABLE orderaudit   (      auditid INT IDENTITY(1, 1) PRIMARY KEY,      orderid BIGINT,      message VARCHAR(200),      logtime DATETIME DEFAULT Getdate()   );

Tables created successfully.

Now we can use these in application.

When a customer places the order then first of all we will get the order ID using the sequence and this ID we can use during the entire process.

Getting the Order Id from the sequence.

DECLARE @OrderId BIGINT = NEXT VALUE FOR SAGA_Demo_Seq; SELECT @OrderId AS GeneratedOrderId;

See the order id

we have generated Order ID even before inserting into database. This is exactly what microservices need. Order ID is born here, and now it will travel everywhere.

--Generating order id DECLARE @OrderId BIGINT = NEXT VALUE FOR SAGA_Demo_Seq; SELECT @OrderId AS GeneratedOrderId;  --We first log in audit table. INSERT INTO OrderAudit(OrderId, Message) VALUES(@OrderId, 'Order Created Event Published');  --Then payment succeeds INSERT INTO OrderAudit(OrderId, Message) VALUES(@OrderId, 'Payment Successful');  --Then inventory reserved INSERT INTO OrderAudit(OrderId, Message) VALUES(@OrderId, 'Inventory Reserved');  --Finally we are inserting the details in order table INSERT INTO Orders(OrderId, CustomerName, Amount, Status_Cd) VALUES(@OrderId, 'Bagesh', 45000.50, 'Completed');

See the records in the table

Here we can easily track the order history. In fact, if our transaction gets failed, we ca track it see below.

 

--Generating order id DECLARE @OrderId BIGINT = NEXT VALUE FOR SAGA_Demo_Seq; SELECT @OrderId AS GeneratedOrderId;   INSERT INTO OrderAudit(OrderId, Message) VALUES(@OrderId, 'Inventory Failed – Running Compensation');   INSERT INTO OrderAudit(OrderId, Message) VALUES(@OrderId, 'Payment Refunded');   INSERT INTO Orders(OrderId, CustomerName, Amount, Status_Cd) VALUES(@OrderId, 'Bagesh', 45000.50, 'Failed');

See the data

So with the help of customer id we can get the step where our transaction got fail.

SEQUENCE offers flexibility, reuse, multi-table support, predictable control, and better manageability than IDENTITY — but it does not guarantee continuity, rollback safety, or gap-free behavior.