Changing a Column to Identity after creation.

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


Author: Imran Akram

A .NET/SharePoint Consultant and a political enthusiast who believes in putting the feet of the elected and/or unelected officials to fire in order to get good governance.

One thought on “Changing a Column to Identity after creation.”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s