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
Reputation: 2,082
Ali Adravi
1 Answer

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

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
Reputation: 2,395
Login to post your answer