Skip to main content
Sep 08, 2017

Default behavior in the SQL Server for adding new row with identity column set to autoincrement is that you are not able to insert new row with specified id column. 

For example, you're not allowed to execute query:

INSERT INTO Students(Id, Name) VALUES(1, 'John')

but only

INSERT INTO Students(Name) VALUES('John')

Id value will be autogenerated for you instead.

The only way to avoid this behaviour is to to set IDENTITY_INSERT for the table that you want to insert new row into.

What is IDENTITY_INSERT

The IDENTITY_INSERT is used to tell a SQL Server's table whether to allow identity column's value insertation or not.

How to enable/disable IDENTITY_INSERT

Here is the sintaxy for enabling IDENTITY_INSERT

-- Turn on IDENTITY INSERTSET

IDENTITY_INSERT table_name ON

The sintaxy for disabling IDENTITY_INSERT

-- Turn off IDENTITY INSERTSET

IDENTITY_INSERT table_name OFF

Note: Only one table can have IDENTITY_INSERT enabled in the single session, i.e. if you turned on identity insertation over one table you are not allowed to turn it on other tables in the same session until you turn it off on the first table.