RSS Feed

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.