How To Set Identity Property to the Current Column in the Table

 

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.

 

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.

 

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.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *