CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Mykroft
Mykroft

Reputation: 13435

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.

Upvotes: 3

Views: 226

Answers (1)

John Sansom
John Sansom

Reputation: 41879

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

Upvotes: 4

Related Questions