Adding an identity column to an existing table

Adding an identity column to an existing table

Below are two methods of adding an identity column to an existing table:

Method: 1

Create a table and insert values in it.

Create TABLE My_Table(Student_id int, name varchar(30) )
Insert into My_Table(Student_id, name)
Values(11, 'name_1'),
(12, 'name_2'),
(13, 'name_3'),
(14, 'name_4')

Select * from My_Table

Student_id	name
11	name_1
12	name_2
13	name_3
14	name_4

Lets check if the Student_id is an identity column or not:

Select columnproperty(object_id('My_Table'),'Student_id','IsIdentity')

Result:
(No column name)
  0

Above statement returns 1 if the column is an identity column, 0 otherwise.

Now lets add an identity column to this My_Table:

ALTER TABLE My_Table
ADD ID INT IDENTITY(1,1) NOT NULL

In the above query, IDENTITY(1,1) means that the seed value is 1 i.e. the value that is used for the very first row and increment value is also 1, meaning that first row has an identity value of 1 and second row has an identity value of 2 (1+1) and third row will have an identity value of 3 (2 +1). In a nutshell the value is incremented by 1.

Select * from My_Table

Student_id	name	ID
11	name_1	1
12	name_2	2
13	name_3	3
14	name_4	4

Lets check again if the newly added ID column is an identity column or not:

Select columnproperty(object_id('My_Table'),'ID','IsIdentity')

Result:
(No column name)
  1

Method: 2

Create a new table with an identity column, copy data to this new table. Drop the existing table followed by renaming the new table.

Create TABLE My_Table(Student_id int, name varchar(30) )
Insert into My_Table(Student_id, name)
Values(11, 'name_1'),
(12, 'name_2'),
(13, 'name_3'),
(14, 'name_4')

select * from My_Table

Student_id	name
11	name_1
12	name_2
13	name_3
14	name_4

As said above, now create a new table with an identity column, copy data to this new table. Drop the existing table (My_Table) followed by renaming the new table.

Lets create a new temporary table (Temp_My_Table), same as My_Table, but here the Student_id column is an identity column.

Create TABLE Temp_My_Table(Student_id int NOT NULL IDENTITY (1, 1), name varchar(30) )

select * from Temp_My_Table

Student_id	name

Copy(insert) data to this new table:

SET IDENTITY_INSERT dbo.Temp_My_Table ON
INSERT INTO Temp_My_Table (Student_id, name)
SELECT Student_id, name FROM My_Table
SET IDENTITY_INSERT dbo.Temp_My_Table OFF

If you try to run the above insert statement without setting
IDENTITY_INSERT on/off, it will show the below error:

Cannot insert explicit value for identity column in table 'Temp_My_Table' when IDENTITY_INSERT is set to OFF.

Lets check the data in both the tables.

select * from Temp_My_Table

Student_id	name
11	name_1
12	name_2
13	name_3
14	name_4

select * from My_Table

Student_id	name
11	name_1
12	name_2
13	name_3
14	name_4

Again confirm if Student_id column of Temp_My_Table is an identity column or not.

Select columnproperty(object_id('Temp_My_Table'),'Student_id','IsIdentity')

Result:
(No column name)
1

Select columnproperty(object_id('My_Table'),'Student_id','IsIdentity')

Result:
(No column name)
0

Drop table My_Table

Rename Temp_My_Table to My_Table:
EXECUTE sp_rename N'Temp_My_Table', N'My_Table', 'OBJECT'

select * from My_Table

Student_id	name
11	name_1
12	name_2
13	name_3
14	name_4

In method 1, we added a new identity column to the table (My_Table). Here, in method 2, we have, in a way, made the Student_id an identity column.