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.




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |