How to change a column to identity–SQL Server

First of all, there is no direct way to do this. You have two options to do this

1. Add all the contents to a new table with Identity column and copy data to that table. Drop the current table and you may rename the table.

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

2. Add a new identity column, and remove the old column. and rename the new column. (You may rename the old column first and then add the identity column with proper name too)

to Rename a column you can use the stored procedure sp_rename like following

EXEC sp_rename 'TableName.OldColumnName', 'NewColumnName', 'COLUMN';

OR

EXEC sp_rename 'DataBaseName.TableName.OldColumnName', 'NewColumnName', 'COLUMN';

to Add a new identity column

ALTER TABLE TableName
ADD ColumnName Int IDENTITY(1,1) Not null

Read More