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.
How to Turn IDENTITY_INSERT On and Off Using SQL Server?
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.