Set Identity_insert on off in identity column in sql server table

When we are creating a table usually we are creating a primary key on an identity column. Identity is auto-incremented. When we insert a new record into our table, this field automatically assigns an incremented value from the previous entry. Usually, we can't insert our own value to this field.

See the example

create table product (  productID int identity(1,1) primary key,  productName varchar(50),  cost int  )

Here I am creating a table with an identity column.

Now I am inserting some records.

insert into product values ('Pen',50)  insert into product values ('Book',50)  insert into product values ('Note Book',50)  insert into product values ('Copy',50)

See the records

Now if we delete a record, the IDENTITY column value for that record is also deleted. If we insert a new record, its value for the IDENTITY column will be incremented from the previous figure in the column. It is not possible to reuse the value that was originally used by the now deleted record.

Suppose by mistake we deleted a record which productID is 3 and this ID are already used on some other table for the reference (Not in foreign key).

This record is deleted from the table.

After deleting this record we realized that we have deleted the wrong record. We need to insert this record.

Let’s see when we are inserting this record with the productID 3.

 If we insert this record with the identity value, in this case, productID will be deferent.

See productId is changed. Which is not expected.

To overcome this issue first of all we need to set the Identity_insert value on after inserting this record we need to set Identity_insert value off.

SET Identity_insert - allow being inserted explicit values into the identity column of a table. The IDENTITY_INSERT statement must be set ON to insert explicit value for the identity column.

SET IDENTITY_INSERT product ON   insert into product   (productID,productName,cost)   values (3,'Note Book',50) SET IDENTITY_INSERT product OFF

Executing this script.

Before execution

Record inserted successfully.

See the record in the table.

Get the expected result.