Working With Identity Column After Table Creation In SQL Server

MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. It allows a unique number to be generated when a new record is inserted into a table. It is used with the syntax given below.

CREATE TABLE City
(
ID int IDENTITY(1, 1) PRIMARY KEY, Name varchar(50)
)

However, once the table is created, we cannot use Alter command to add an identity to the table. Use Alter command to add an identity will throw an exception. If we go to the design of the table, we can see a property named ‘Identity Specification’ that can be set to enable the identity.

Even in the design mode, it will be disabled/grayed out. This is the case, if we have a Primary key set on the column that we are trying to add an identity.

Set Identity to Non Primary Field

However, if the column ‘Id’ was not set as the Primary key, we can set the identity from the Design mode, as shown below.

In the upcoming section, we will see the various options to add the identity to an existing table.

Add an identity column

One option to add an identity to the table is to add a new column to the table and set it as an identity. This is possible through the Alter statement. However, if we want to set the identity to an already existing column in the table, we cannot use this DDL command.

Alter Table City Add CityId int Identity(1,1)

Moreover, if we try to add an additional identity column to an already created table, using Alter command given below, it will throw the exception as only one identity column can be specified for a table.

Alter Table City Add NewIdentity int Identity(1,1)

Create a new identity column and rename it to an existing column after dropping it

Another option if we want the identity column to be applied to an existing column name is

Add a new column and set it as an identity.
Remove the old column.
Rename the new column to the old column name.

In this way, we get the feeling that the identity has been applied to the existing column in the table.

Script

Alter Table City
Add CityId Int Identity(1, 1)
Go
Alter Table City Drop Column Id
Go
Exec sp_rename 'City.CityId', 'Id', 'Column'

Prior to running the script, the table values for the Id column are given below.

After running the script, we can see that the Id column has been overwritten with the new Identity values, which causes data loss in the column.

Create an identity column by creating the table without any data loss

One viable option to prevent the data loss associated with the previous approach is to recreate the table with the identity column and load the data into it. The steps followed in this approach are given below.

Create a temporary table with the identity column.
Copy the data from the original table into the temporary table.
Drop the original table.
Rename the temporary table to the original table name.

Script

CREATE TABLE dbo.Tmp_City(Id int NOT NULL IDENTITY(1, 1), Name varchar(50) NULL, Country varchar(50), )
ON[PRIMARY]
go
SET IDENTITY_INSERT dbo.Tmp_City ON
go
IF EXISTS(SELECT * FROM dbo.City)
INSERT INTO dbo.Tmp_City(Id, Name, Country)
SELECT Id,
Name, Country
FROM dbo.City TABLOCKX
go
SET IDENTITY_INSERT dbo.Tmp_City OFF
go
DROP TABLE dbo.City
go
Exec sp_rename 'Tmp_City', 'City'

In this way, the identity will be set to the column as well as the data will be preserved.

Use Generate Scripts Option

The approach given above can be automated by generating the script, which will create the table along with the data. In this approach, we will use generate scripts option available at the DB Level. The steps are given below.

Get the script to create the table along with the data, using ‘Generate Scripts’ option.
Add identity to the generated script.
Drop the existing table and run the generated script.

On right clicking the database containing the table, select Tasks -> Generate Scripts.

This will open 'Generate and Publish Scripts Window'.

Select the table for which we want to generate the script.

Select Save to the file radio button and click Advanced.

Change type of data to script from ‘Schema’ to ‘Schema and data’.

Proceed to next page.

This will complete the generation of the script.

Going ahead to the script location we had specified, we can see the table creation script along with the data to be inserted. We can now add the identity keyword to the script and run it after dropping the existing table.

However, when there are gigs of data, the above approaches of recreating the table and re inserting the data are not efficient. We will see how to overcome that in the final approach.

Recreate the table with Identity and use Partition Switching

The above methods can be quite time consuming if there are millions of records present in the table. To speed up the data population after table creation with Identity specification we can transfer the data using partition switching. The steps followed in this process are,

Create the table with the same schema as the original table and include identity column.
Use Switch to transfer the partition from old table to new table.
Drop the old table.
Rename the new table to the old table.

This is much faster than inserting all the records back to the new table as inserting is an expensive operation.

Script

CREATE TABLE dbo.Tmp_City(Id int NOT NULL IDENTITY(1, 1), Name varchar(50) NULL, Country varchar(50), )
ON[PRIMARY]
go
SET IDENTITY_INSERT dbo.Tmp_City ON
go
Alter Table City
switch to Tmp_City;
go
DROP TABLE dbo.City
go
Exec sp_rename 'Tmp_City', 'City'

Summary

Identity column is a great way to enable an auto increment in the table but we will have to keep in mind the points given below, failing which, we will end up using one of the methods given above to add an identity to the table.

Always make the design consideration, while choosing an identity for a table, as adding it post creation of the table becomes a tedious task, when there are millions of records present in the table.
Try not to set the Identity column as the Primary key. Primary Key constraint will prevent the users from adding an identity to that column from the Design page after the table creation.