How to Add Identity to Existing Column

This article is in continuous with the previous article on "Adding Identity to an existing table in SQL Server".

Back ground is I want to add IDENTITY column to the which is already exisitng with valid data.

Step 1: Add Column to the Table
ALTER TABLE DOMAIN ADD DOMAIN_ID_NEW INT IDENTITY (1, 1)

Step 2: Verification of the IDs
SELECT * FROM DOMAIN where DOMAIN_ID_NEW <> DOMAIN_ID

Step 3:
If the above query return 0 records then it means, there is no difference between the IDs which came from Old table and the IDs which are generated from Identity Column.

Step 4: Drop column in the table
ALTER TABLE DOMAIN DROP COLUMN DOMAIN_ID

Step 5: Rename the Column
sp_RENAME 'DOMAIN.DOMAIN_ID_NEW', 'DOMAIN_ID','COLUMN'

Step 6: Over


Related Articles:
Rename Table in SQL Server
Create Table with Identity in SQL Server
Identity in SQL Server - Alter Identity Column in SQL Server