How To Remove the Identity Column in SQL Server

Setting the identity property on a table when it is created is a simple task. The below snippet will create table called SampleTable with ID column which has an identity property.

CREATE TABLE SampleTable
(
ID INT IDENTITY(1,1),
Name VARCHAR(30)
)

To set an indentity column for an alredy created table using SQL Server Management Studio, is simply matter of setting the identity property of the column.

However, to remove an indentity column using SQL?

Step 1, create a temporary table with relevant identity property set to the required column.

--Step 1
CREATE TABLE dbo.Tmp_SampleTable
       (
ID int NOT NULL IDENTITY (1, 1),
       Name varchar(30) NULL
)  ON [PRIMARY]

Step 2, lock the temporary table so that , no one will be able to insert data into the new table.

--Step 2
ALTER TABLE dbo.Tmp_SampleTable SET (LOCK_ESCALATION = TABLE)

Step 3, since the new table has an identity property, IDENTITY_INSERT set to ON.

--Step 3
SET IDENTITY_INSERT dbo.Tmp_SampleTable ON

Step 4, Insert to the newly created table from the previous table. During this operation exclusive table lock will be placed on the previous table. 

--Step 4
IF EXISTS(SELECT * FROM dbo.SampleTable)
        EXEC('INSERT INTO dbo.Tmp_SampleTable (ID, Name)
SELECT ID, Name FROM dbo.SampleTable WITH (HOLDLOCK TABLOCKX)')

Step5, set the identity off for the new table.

--Step 5
SET IDENTITY_INSERT dbo.Tmp_SampleTable OFF

Step6, Drop the previous table

        DROP TABLE dbo.SampleTable

Step7, Rename the tmp table to the correct name.

--Step 7
 EXECUTE sp_rename N'dbo.Tmp_SampleTable', N'SampleTable', 'OBJECT' 

If you are running this for a large table, Step 4 will take considerable duration. During this time, table is not available.

]]>

Leave a comment

Your email address will not be published.