views:

128

answers:

1

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
This seems to correspond to what I found through Google. Since this is not a temporary change IDENTITY_INSERT doesn't help me.
Mykroft
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