Steps to add Add Identity Column to Table in SQL Server
The identity column uniquely identifies a row in SQL Server. You can have only one identity column per table.
Define an identity column using CREATE TABLE statement
The following create table declares a column as identity. It takes the default seed value as 1 and the increment value as 1. It means the first row will have an identity value of 1, and the next row will get one increment, so its value will be 2.
Create table Emp ( ID INT IDENTITY(1,1) )
Define an identity column using ALTER TABLE statement
You can use alter table statement to add an identity column for an existing table.
ALTER TABLE EMP ADD ID INT IDENTITY(1,1) NOT NULL
You cannot alter an existing column as an IDENTITY column. Use the steps below if you need to alter an existing column for identity property.
You can create a new column as an identity column
Drop the old column
Rename the old column as the original column name.