Search code examples
sqlsql-servert-sql

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?

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