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.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE [dbo].[IdentitiyExampleTable]( [ID] [int] NULL, [Name] [varchar](250) NULL ) ON [PRIMARY] GO 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/ BEGIN TRANSACTION 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 COMMIT BEGIN TRANSACTION GO CREATE TABLE dbo.Tmp_IdentitiyExampleTable ( ID int NOT NULL IDENTITY (1, 1), Name varchar(250) NULL ) ON [PRIMARY] GO ALTER TABLE dbo.Tmp_IdentitiyExampleTable SET (LOCK_ESCALATION = TABLE) GO SET IDENTITY_INSERT dbo.Tmp_IdentitiyExampleTable ON GO IF EXISTS(SELECT * FROM dbo.IdentitiyExampleTable) EXEC('INSERT INTO dbo.Tmp_IdentitiyExampleTable (ID, Name) SELECT ID, Name FROM dbo.IdentitiyExampleTable WITH (HOLDLOCK TABLOCKX)') GO SET IDENTITY_INSERT dbo.Tmp_IdentitiyExampleTable OFF GO DROP TABLE dbo.IdentitiyExampleTable GO EXECUTE sp_rename N'dbo.Tmp_IdentitiyExampleTable', N'IdentitiyExampleTable', 'OBJECT' GO COMMIT |
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.
|
1 2 |
ALTER TABLE dbo.IdentitiyExampleTable DROP COLUMN ID ALTER TABLE [dbo].[IdentitiyExampleTable] ADD ID INT IDENTITY (1, 1) NOT NULL; |
