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:
CREATE TABLE City
(
ID int IDENTITY(1,1) PRIMARY KEY,
Name varchar(50)
)
However, once the table has been created, we cannot use the Alter command to add an Identity to the table. Using Alter Command to add 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 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.
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 various options to add Identity to an existing table.
One option to add an Identity to the table is to add a new column to the table and set it as 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 the below Alter command, 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)
Another option if we want the identity column to be applied to an existing column name is:
This way we get the feeling that the identity has been applied to the existing column in the table.
Alter Table City
Add CityId Int Identity(1, 1)
Go
Go Exec sp_rename 'City.CityId', 'Id', 'Column'
Prior to running the script the table values for the Id column was:
After running the script we can see that the Id column has been overwritten with new Identity values causing data loss in the column.
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 is:
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
IF EXISTS ( SELECT *
FROM dbo.City )
INSERT INTO dbo.Tmp_City ( Id, Name,Country )
SELECT Id,
Name,Country
FROM dbo.City TABLOCKX
SET IDENTITY_INSERT dbo.Tmp_City OFF
DROP TABLE dbo.City
Exec sp_rename 'Tmp_City', 'City'
This way we the identity will be set to the column, as well as the data, will be preserved.
The above approach can be automated by generating the script that will create the table along with the data. In this approach, we will use the Generate scripts option available at the DB Level. The steps are:
Upon right-clicking DB , Select Tasks -> Generate Scripts
This will open up the Generate and Publish Scripts window.
Select the table for whom we want to generate the script.
Select Save to file radio button and click Advanced.
Change Type of data to script from ‘Schema’ to ‘Schema and data’.
Proceed to the 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 above approaches of recreating the table and re-inserting the data is not efficient. We will see how to overcome that in the final approach discussed in this article
Above methods can be quite a 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:
This is much faster than inserting all the records back to the new table as Insert is an expensive operation. Partition Switching on the other hand does only meta data updates to the location of the existing data and no data is moved or duplicated making it faster and efficient.
Alter Table City switch to Tmp_City;
Identity Column is a great way to enable auto increment in the table. But we will have to keep in mind the below points failing which we will end up using one of the above methods to add Identity to the table.