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.

Introduction to IDENTITY_INSERT in SQL Server

The identity column is the numeric column in the SQL Server table whose value increments automatically. The value of the identity column is set by the SQL Server itself based on the definition provided by the user. Thus user can not explicitly set the value of such columns and they are unique. The identity column can not be NULL.

Syntax:

IDENTITY [( seed, increment)]

Explanation: Here, seed means the first value to start with while loading the data into the table and increment is the value added to the previous identity value of the column.

Examples of How to Turn IDENTITY_INSERT On and Off Using SQL Server

Example 1: Create a Table With an Identity Column and See it Functions

Let's Create a database named "GeeksForGeeks" with a table named "students" to store student records. The table should include columns for student ID, first name, last name, and mentor. We will also Insert sample student records into the table.

Query:

USE GeeksForGeeks

CREATE TABLE students 
(Id int identity(1,1), FirstName varchar(50), LastName varchar(150), Mentor varchar(50))

INSERT INTO students values
('Raman', 'Raghuvanshi', 'Namita Thapar'),
('Raghav', 'Jain', 'Swati Pathak'),
('Jolly', 'Modi', 'Prerna Patil'),
('Shubham', 'Sambal', 'Rohit Raina')

SELECT * FROM students

Output:

Explanation: We have created identity column with seed value 1 and incremented it with 1, and you must notice that while inserting the data, Id column is not mentioned and it output table we can see its value as SQL Server automatically inserts data based on seed and increment value. If we change the seed value to 100 and increment to 2, then values of the Id column will be 100, 102, 104, etc.

What if we tried to include the Id column while inserting the data in the table.

Example 2: Try to Insert Explicit Value for Id Column

Let's Write a query to insert a new student record into the "students" table with the given details: FirstName - 'Raman', LastName - 'Raghuvanshi', Mentor - 'Namita Thapar'. We will ensure the "Id" column, an identity column, is automatically generated by the database.

Query:

INSERT INTO students values
(1, 'Raman', 'Raghuvanshi', 'Namita Thapar')

Output:

Explanation: The error says we can not insert the explicit value in the table where identity on the column is defined. And it even suggests us the way to do it by using the IDENTITY_INSERT property.

So, now let's solve our issue by using the IDENTITY_INSERT property.

How to Turn IDENTITY_INSERT ON SQL Server 2008

IDENTITY_INSERT ON statement that allows us to explicitly specify the value we want to insert into the identity column of a table, instead of depend on the database to automatically generate the next value in the sequence.

Syntax:

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

Explanation: Values mentioned in square brackets are optional. This syntax is applicable to SQL Server 2000 and later versions. So it covers SQL Server 2008.

Example 1: Insert Explicit Value by Set IDENTITY_INSERT to ON

Let's consider the situation where we have to insert the students data having id = 10 for some reason. We can not insert the row data directly like this, so in order to insert explicit value we need to set IDENTITY_INSERT to ON.

Query:

SET IDENTITY_INSERT students ON

INSERT INTO students 
(ID, FirstName, LastName, Mentor)
values
(10, 'Piyush','Goyal', 'Virat Gupta'),
(20, 'Pankaj','Varma', 'Prakash Jha'),
(30, 'Sadhana','Chavhan', 'Swapnil Patil')

SELECT * FROM students

Output:

Explanation: We have set IDENTITY_INSERT value of the table students to ON and then inserted the data and notice that we have mentioned the columns list this time and it was not mentioned in the above 2 examples, so whenever we are using identity insert we have to mention the identity columns in the column list and then we can insert whatever value we want to insert even if it is duplicate as identity column does not is not unique unless provided in the definition.

Example 2: Get Back to out Routine Where SQL Server Automatically Insert Values in Id Column

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.

Query:

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

Output:

Explanation: We faced the error while insert the data normally just like used in example 1, because we have set identity insert to on. So when we want to insert data as per identity definition of seed and increment we have to set the identity insert value to off. We can not set to OFF unless it is already ON.

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.

Conclusion

In summary, we have understood the core concept of the identity column in SQL Server and the significance of Identity Insert option between ON and OFF. The identity column streamlines the generation of unique values, simplifying the management of primary keys. Turning Identity Insert ON allows for explicit value insertion, beneficial in scenarios like data imports or migrations. Conversely, setting it to OFF maintains automatic generation, ensuring data integrity.