Today I had to change the Primary key columns in tables so that they can become identity columns but I couldn’t do it using Alter Table x Alter column y IDENTITY(1,1) command. So I found out that you can’t use ALTER TABLE to change a column into IDENTITY column (except on SQL Server CE!). One way is to rename the table, create a new and move over the data. You need to have SET IDENTITY_INSERT
on for the table when you move the data.
You can also do it in Enterprise Manager – which will rename and move data behind the scenes.
Thanks to this thread