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

Note:

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.