How do I remove an Identity completely

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.

From stackoverflow

• Hi,

  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

  Mykroft : This seems to correspond to what I found through Google. Since this is not a temporary change IDENTITY_INSERT doesn't help me.

  HLGEM : Perhaps one of the reasons why this is complex to do is to make you rethink doing it at all! This is not a recommended refactoring of a database usually.