SQL Server: How to remove IDENTITY from a Column?

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

How to remove IDENTITY from a Column of a SQL Server?
The one line answer is – There is no specific way for disabling the IDENTITY on a Table Column.

There are few hacky ways to do this so let me share here.

Option 1:

• Create a replica blank table without any constraint
• Transfer all data from your main table to replica table
• Apply proper constraints like a Primary key or Unique key on that column which is identity column of the main table
• Dropped the main table
• Rename your replica table to the original table name

 

Option 2:

• Create a replica column in the same table
• Update new column by a value of IDENTITY Column
• Check the reference of IDENTITY Column and If any then remove temporary
• Drop the IDENTITY Column
• Rename the replica column to the original column name