How do I remove an Identity completely

Question

I'm currently putting together some changes in our data model which include changing a column (that happens to be part of the primary key) so that it is no longer an identity. Is there a way to do this short of actually removing and recreating the entire column or table? The autogenerated code from SSMS does just that but I was wondering if there was perhaps a simpler solution.

Answer 1

You cannot remove the Identity property of a column without droping it.

Possible solution steps are:

(a) Add a new column

(b) Update the column with identity column value

(c) Remove the identity column.

Alter Table Tablename Add newColumnname int

Update Table set newColumnname =IdentityColumn

Alter Table TableName Drop IdentityColumnName

Note : If you are talking about switching off the identity property for the time being, check Set IDENTITY_INSERT TableName ON Command

Make sense?

Cheers, John