Remove the Identity property of a column in SQL Server 2008

I have a situation where I need to update the identity column value so want to update the schema

Need to alter the table and finally make it again back. like this:

ALTER Table TableName remove identity property
.....................
other workd
.....................
ALTER Table TableName Add identity property back

sql server
identity

Asked: 16 May 2012

Ali Adravi

Reputation: 2,082

1 Answer

You can simply drop the identity property form a table by using:

ALTER TABLE TableName
Drop StateID

Where StateID is the Identity  column.
After running the above statement you identity property will be removed.

Before leaving let me explain what happens when you insert or delete the identity property:

In both case you existing table records with me moved to a new table named "TableName1" and moved all the record to that table and rename back to your original table name.

so when you will have more records, you will never like to do this in procedures.

Hope it will help you!!

Answered: 16 May 2012

Hamden

Reputation: 2,395