How to change a column to an IDENTITY column and preserve the data?

Question

I had a column which was IDENTITY(1,1). All rows had been numbered consecutively. I did not add or remove any rows. I removed the identity property on this column. Now, I want it back. I think i can reset it to identity with seed = biggest number in existing table.

Could any problems arise if I do this ?

Answer 1

From MSDN SQL Server forum - You have 2 options:

1. Create a new table with identity & drop the existing table
2. Create a new column with identity & drop the existing column.

*But take special care when these columns have any constraints / relations

As stated in MSDN documentation about considerations for preserving data during deployment and schema Updates:

Changes to properties of an identity column on a table, such as the identity seed - "Requires data motion" and it can, in some cases, cause data loss during its execution of the synchronization.

The recommendation in this case: "If the deployment or update script contains these types of changes, you might want to modify it manually to preserve the data"