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.