Question: Is it possible to add an auto incremental identity column to any table in SQL Server after creating a table.
Answer: There are two answers – No and Yes. Let us see them one by one.
Answer No – If you have an integer column in your table and you want to convert that column to identity table. It is not possible with the help of SQL Server. You will just have to add a new column.
Answer Yes – If you want to add new column to the table, it is totally possible to do so with the help of following a script.
ALTER TABLE YourTable ADD IDCol INT IDENTITY(1,1)
If you want to convert your old column to int column, may be you can drop that first and add a new column identity right after that with the help of following a script.
ALTER TABLE YourTable DROP COLUMN IDCol ALTER TABLE YourTable ADD IDCol INT IDENTITY(1,1)
Let me know if you have any other work around besides SSMS (as that option just drops table and recreates it).
Reference: Pinal Dave (https://blog.sqlauthority.com)
