sql - Remove Identity from a column in a table

Question

We have a 5GB table (nearly 500 million rows) and we want to remove the identity property on one of the column, but when we try to do this through SSMS - it times out.

Can this be done through T-SQL?

Answer 1

You cannot remove an IDENTITY specification once set.

To remove the entire column:

ALTER TABLE yourTable
DROP COLUMN yourCOlumn;

Information about ALTER TABLE here

If you need to keep the data, but remove the IDENTITY column, you will need to:

• Create a new column
• Transfer the data from the existing IDENTITY column to the new column
• Drop the existing IDENTITY column.
• Rename the new column to the original column name