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.
+3
A:
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
John Sansom
2009-03-03 21:43:57
This seems to correspond to what I found through Google. Since this is not a temporary change IDENTITY_INSERT doesn't help me.
Mykroft
2009-03-03 21:47:51
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.
HLGEM
2010-08-25 17:57:40