SQL Server Performance Forum – Threads Archive

Add "Identity" to an existing column

Is it possible to add an Identity column to an existing column.
The following does not work? ALTER TABLE CPM_Staging..Product
ALTER COLUMN [ProductId] [int] IDENTITY (1, 1) NOT NULL Current Schema has a ProductID field as an integer, involved in a primary key that spans another column. Any ideas? Regards,
Rik

---

One way is: Delete Key. Rename ProductId to other column name, create ProductId with Identity and recreate Key.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

---

Another way would be to add a new column, assign the identity property, drop ProduktID column and rename the former to ProduktID.
When adding the Identity ‘column’, do you still want to have a composite PrimaryKey? Frank
http://www.insidesql.de
http://www.familienzirkus.de

---

]]>