Understanding IDENTITY_INSERT

Understanding IDENTITY_INSERT in SQL Server

Many of us will get a basic question on whether the IDENTITY_INSERT is ON or OFF on a SQL Table. We also have some confusion between IDENTITY and IDENTITY_INSERT.

In this article i will explain the basic use of IDENTITY_INSERT and how it exactly works.

Before understanding the IDENTITY_INSERT we need to know what is an identity column. It is column which keeps on incrementing without supplying the value explicitly during insertion.

In the below table EID is an identity column which will be incremented by 1 when a new record is inserted.

 

Now, for example let say I have a requirement to insert a EID with value as ‘10’, just below of this value. You can insert a custom value in two ways:

1. Either you turn off the Identity property by going into design, and opting for column properties, i.e., using GUI. After inserting that value, again making it ON, so that it will continue incrementing it.
2. Or using T-SQL code, as shown below:

SET IDENTITY_INSERT dbo.Customers ON;

INSERT INTO dbo.Employee VALUES (10, 'EmpName10') you will get an error,

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table ‘dbo.Employee’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

so you need to mention the Columns explicitly during insert as show below:

INSERT INTO dbo.Employee(EID,ENAME) VALUES (10, 'EmpName10')
Now, if you do a select * from Employee you will get the result as shown below,

And now if you want to continue to insert the next identity numbers you shouldn't forget to make the IDENTITY_INSERT Property to OFF on that table,

SET IDENTITY_INSERT dbo.Customers OFF;

In Simple words,

If you make IDENTITY_INSERT ON on a table you have to insert value in the identity column explicitly during insertion. If the IDENTITY_INSERT is OFF on a table it mean the value in the identity column will be auto generated.

In case if you are trying to insert some custom values in identity column make sure you set the IDENTITY_INSERT property to OFF once you have done with your insertion else the values in the identity column will not be auto generated.

Happy Coding!