Remove Identity from a column in a table
sql, sql-server, t-sql
Solution
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
Problem
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?