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.