SQL Server Set identity_insert

Are you aware of the SQL Server Set identity_insert function? If not, follow this tutorial to learn more about this.

What is identity_insert?

The IDENTITY_INSERT tells an SQL Server’s table whether to allow the identity column’s value to be inserted.

Use identity_insert in Table

Let’s look into the simple creation of a table using the identity_insert function.

Insert into Student values ('Alaska')
Insert into Student values ('John')

select * from Student 
SQL Server identity off

The integer value will automatically be added to the StudentId using the identity function.

Let’s see another example: if the user does not want the identity to be in sequential order, use the syntax below.

set identity_insert Student ON;
Insert into Student (StudentId, studentname) values (20,'peter');

select * from Student 
SQL Server Identity insert


Based on everything mentioned above, using an identity insert to a column in SQL Server will significantly improve databases and data integrity. It will also permanently improve performance.

You may like to read: