Before reading this article, I suggest you read my article “Identitiy Insert On SQL Server” to see what the identitiy property is in the table.
This article describes how to set an identitiy property to an existing column in a table.
You can do this with two method:
1. Create a new table with the Identitity property and transfer the existing records to this new table
2. Add a new column with identity to the table and delete the old column
First of all, with the help of the following script, let’s create a table that contains the ID and Name columns and the identity property is not set.
CREATE TABLE [dbo].[IdentitiyExampleTable](
[ID] [int] NULL,
[Name] [varchar](250) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[IdentitiyExampleTable]([ID],[Name])VALUES (1,'Nurullah CAKIR')
INSERT INTO [dbo].[IdentitiyExampleTable]([ID],[Name])VALUES (3,'Faruk ERDEM')
INSERT INTO [dbo].[IdentitiyExampleTable]([ID],[Name])VALUES (2,'Hakan GURBASLAR')
Let’s perform these operations on our example table with the two method mentioned above.
1. Create a new table with the Identitity property and transfer the existing records to this new table:
Right click on the table and click the design as below.
By clicking on the column we want to set Identity property, we open the tree structure of Identity Specification from below and double click on where you see No in Identity section. The values will change as you see on the screen below.
Then we click Table Designer->Generate Change Sript to get the script of the changes.
A script is generated as follows.
/* To prevent any potential data loss issues,
you should review this script in detail before running it outside the context of the database designer.*/
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
CREATE TABLE dbo.Tmp_IdentitiyExampleTable
ID int NOT NULL IDENTITY (1, 1),
Name varchar(250) NULL
) ON [PRIMARY]
ALTER TABLE dbo.Tmp_IdentitiyExampleTable SET (LOCK_ESCALATION = TABLE)
SET IDENTITY_INSERT dbo.Tmp_IdentitiyExampleTable ON
IF EXISTS(SELECT * FROM dbo.IdentitiyExampleTable)
EXEC('INSERT INTO dbo.Tmp_IdentitiyExampleTable (ID, Name)
SELECT ID, Name FROM dbo.IdentitiyExampleTable WITH (HOLDLOCK TABLOCKX)')
SET IDENTITY_INSERT dbo.Tmp_IdentitiyExampleTable OFF
DROP TABLE dbo.IdentitiyExampleTable
EXECUTE sp_rename N'dbo.Tmp_IdentitiyExampleTable', N'IdentitiyExampleTable', 'OBJECT'
As you can see in this script, it creates a new table, transfers the records in the current table to the new table, and deletes the old table and changes the name of the newly created table.
If your table is large, this process will take a long time and your system will not work as long as this process is in progress, but this is the only way you can work if your existing ID values need to be protected.
Also, if your table is too large, make sure that the disk containing the log file is sufficient for you.
2) Add a new column with identity to the table and delete the old column:
With the help of the following script, you can delete the existing column that you want to make the identity and add a new column with the identity property to the table. But in this way the values of your existing ID column will be deleted and re-created from scratch.
If you want the current values in the ID column not to be deleted, I recommend that you use method 1. As an additional information, method 2 will be completed much faster than method 1.
ALTER TABLE dbo.IdentitiyExampleTable DROP COLUMN ID
ALTER TABLE [dbo].[IdentitiyExampleTable] ADD ID INT IDENTITY (1, 1) NOT NULL;