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.

Table of Contents

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

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

Conclusion

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:

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.