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.