Conrad Jagger Conrad Jagger - 1 month ago 17
SQL Question

Remove Identity from a column in a table

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

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