How to remove an identity from a column in SQL Server

Removing an identity in SQL Server

We have a table with an identity column. We have to delete the identity from this column.
Unfortunately, in SQL Server, once we have set an identity, we cannot delete it: to remove an identity we must drop the column.


If we want to preserve the data in our column, we must follow some simple steps.
Let's see these steps with an example.


This is our table:

CREATE TABLE dbo.Log
    (
    ID           INT IDENTITY NOT NULL,
    ERROR        VARCHAR(1000),
    STACKTRACE   VARCHAR(1000),
    ...
    CONSTRAINT   PK_LOG PRIMARY KEY (ID)
    )
GO



1. add a new column identical to the column with the identity

ALTER TABLE Log ADD ID_Temp INT


2. copy the content of the column ID into the new column

UPDATE Log SET ID_Temp = ID


3. drop the old column

ALTER TABLE Log DROP COLUMN ID

Actually, in our example table we have to drop the primary key before we can drop the column, so the right sequence of statements is this one:

ALTER TABLE Log DROP PK_LOG
ALTER TABLE Log DROP COLUMN ID


4. rename the new column

sp_rename 'dbo.LOG.LGNUM2.ID_Temp', 'ID', 'COLUMN'


5. in our example we should also establish the primary key again

ALTER TABLE Log ADD PRIMARY KEY (ID)


In this way we can in the end delete an identity, preserving all existing data in our table.