How to modify the existing column property as IDENTITY

We have 3 options to modify the existing column as IDENTITY in SQL Server.

          1. Through SQL Server Management Studio, we can modify it.

          2. Create a new table with IDENTITY Column & drop the existing table

-- In this Approach we can retain the existing data values on the newly created identity column

          3. Create a new column with IDENTITY & drop the existing column

-- In this approach we can’t retain the existing data values on the newly created identity column

Option:1 ==> Through SQL Server Management Studio, we can modify it.

1.   Connect to your DB

2.   Show the table/column names in object explorer window

3.   Right click on column name

4.   Select modify

5.   In column properties tab

1)   Alter "identity specification"/"is identity" to YES

2)   Set increment/seed properties as desired

Note:  if we leave it at default 1, 1 it will automatically insert the next row with the appropriate next value (not 1)

Option:2 ==> create a new table with IDENTITY Column & drop the existing table

1. Create a new table with the desired layout with IDENTITY Column
2. Move the old table’s data into this new table
3. Drop the old table
4. Rename the new table to the old table
5. Add back any constraints and/or indexes

Tested Code:

Create table emp(empid int, empname varchar(50))

Go

Insert into emp values(1, 'James')

Insert into emp values(2, 'Carolin')

Insert into emp values(3, 'Lewis')

Insert into emp values(4, 'Tornato')

Insert into emp values(1, 'Mary')

Insert into emp values(8, 'Tony')

Go

Select * from emp

Go

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

CREATE TABLE Tmp_emp

      (

      empid  int NOT NULL IDENTITY (1, 1),

      empname varchar(50)

      )  ON [PRIMARY]

GO

SET IDENTITY_INSERT Tmp_emp ON

GO

IF EXISTS(SELECT * FROM emp )

       EXEC('INSERT INTO Tmp_emp  (empid , empname)

            SELECT empid ,empname FROM emp  WITH (HOLDLOCK TABLOCKX)')

GO

SET IDENTITY_INSERT Tmp_emp OFF

GO

DROP TABLE emp

GO

EXECUTE sp_rename N'Tmp_emp ', N'emp ', 'OBJECT'

GO

COMMIT

Option:3 ==> create a new column with IDENTITY & drop the existing column

Alter Table emp Add empid_new Int Identity(1,1)

Go

Alter Table emp Drop Column empid

Go

Exec sp_rename N'emp.empid_new ', N'empid ','COLUMN'