How To Turn Off Identity Column In SQL Server

by

I recently participated in a webinar, and one guy asked me about the possible approaches to disabling the identity column in SQL Server. Let me summarize the information for you.

To turn off the identity column in SQL, you must set the IDENTITY_INSERT property to OFF.

Let’s look at the complete steps with an example to clarify how to do this and the effect after disabling this option.

Suppose we have a table called Orders with an identity column named OrderID. The screenshot below shows this.

turn off identity column in SQL Server

Now, we can turn off the identity column using the below query. Check out the screenshot below for your reference.

Check out: How To Insert Values Into Table With Identity Column In SQL Server

SET IDENTITY_INSERT Orders OFF;
How to turn off identity column in SQL Server

After I turned off the identity column, I encountered the error below when inserting a record and explicit value for the identity column. Check out the screenshot below for your reference.

INSERT INTO Orders (OrderID, OrderName, OrderType) VALUES (4,'finance', 'GST');
how to off the identity column in sql server

“Cannot insert explicit value for identity column in table ‘Orders’ when IDENTITY_INSERT is set to OFF.”

Conclusion

The identity column is turned off by default in SQL Server. Still, if you have turned it on for any of your requirements, it is suggested that you turn it off by setting the IDENTITY_INSERT Orders to OFF using the information mentioned in this article.

You may also like following the articles below.