How to remove identity column in SQL Server

Recently, my team received a requirement to remove an identity column from one of our critical tables. Before trying to do it, we backed up the data for the identity column for the safer side. Then, we followed the simple approaches below to remove the column.

Approach-1: Suggested approach

You can not just blindly delete the identity column like a regular one. Still, the suggested approach is to follow the steps below to ensure you are not losing any data.

1. First, you create a brand new column to store the data temporarily.
2. Copy the data from the identity column to the brand-new column you created above.

ALTER TABLE TableName ADD Newcolumn INT
UPDATE TableName SET Newcolumn = OldColumn

Let’s consider an example of the Orders table as shown below

ALTER TABLE Orders ADD NewOrderID INT
UPDATE Orders SET NewOrderID = OrderID

Check out How To Add Identity Column To Existing Table In SQL Server

3. Now, you can drop the existing identity column.

ALTER TABLE Orders DROP COLUMN OrderID

4. Then, Recreate the column without the identity property

ALTER TABLE Orders ADD OrderID INT

5. Copy the backup data.

UPDATE Orders SET OrderID = NewOrderID

6. Finally, Drop the temporary column using the below query.

ALTER TABLE Orders DROP COLUMN NewOrderID

Check Out: How to turn off identity column in SQL Server

Approach-2: Using right click

To remove or delete the identity column, right-click on the column name and choose the delete option, as shown in the screenshot below.

Conclusion

Modifying an IDENTITY column can significantly affect your database design and any dependent applications. Always have a backup and thoroughly test your changes before implementing them in a production environment.

You may also like following the articles below.

• How to check identity column in SQL Server
• How to reset identity column value in SQL Server
• How To Insert Values Into Table With Identity Column In SQL Server