How to Turn IDENTITY_INSERT OFF SQL Server 2008

IDENTITY_INSERT OFF is the default state for a table’s identity column. It signifies that automatic identity value generation is enabled, meaning the database assigns the next sequential value whenever we insert a new row without specifying a value for the identity column.

Syntax:

SET IDENTITY_INSERT [ [ database_name.] schema_name.] table_name { OFF }

Explanation: This syntax is applicable to SQL Server 2000 and later versions. So it covers SQL Server 2008

Example 1: Let’s Solve Above Error and Set Identity Insert to OFF.

Let’s Insert a new record into the “students” table with the values ‘Madhura‘ for FirstName, ‘Sharma‘ for LastName, and ‘Satish Kumar‘ for Mentor. Ensure that the identity column “ID” is automatically incremented, and turn off the IDENTITY_INSERT option after the insertion.

Query:

SET IDENTITY_INSERT students OFF

INSERT INTO students values
('Madhura', 'Sharma', 'Satish Kumar')

SELECT * FROM students

Output:

Explanation: We resolved above error by setting IDENTITY_INSERT of students table to OFF which informs SQL Server to function normally as per definition of the identity column. We can see that Id of the row 8 which is the latest data inserted in 31 because SQL Server increments the value based on previous value of the column.

So for the best practice, whenever it is required to insert explicit values in the identity column set the IDENTITY_INSERT to ON and once insertion is done set IDENTITY_INSERT to OFF.

IDENTITY_INSERT in SQL Server is a valuable tool in SQL Server 2008, allowing us to control how identity values are assigned when inserting new records into a table. IDENTITY_INSERT ON is a Transact-SQL statement that allows us to explicitly specify the value we want to insert into the identity column of a table.

In this article, we will understand How to turn IDENTITY_INSERT on and off using SQL Server 2008 with multiple examples and so on.