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.