How to Add an IDENTITY to an Existing Column in SQL Server

It enables you to store, organize, and manipulate data in a relational format, meaning data is organized into tables. It Stores and manages data for dynamic web applications, ensuring effective user experiences. In this article, we will learn about How to add an identity to an existing column in SQL Server using examples and so on.

IDENTITY to an Existing Column

There are times when we need to modify the structure of our tables. One common requirement is to add an identity property to an existing column in the SQL Server.

In SQL Server, The identity column uniquely identifies a row. we can have only one identity column per table. This can be beneficial for creating primary keys or other scenarios where unique, incremental values are required.

We can use IDENTITY to define a column with auto-increment values. It auto-generates a new unique number when inserting a new record into the table.

Syntax:

IDENTITY [ (seed , increment) ] 

• Seed: The identity column’s first value is known as the seed. If we specify a seed, SQL Server will begin generating identity values starting from this seed. For example, if the seed is 1, the first identity value will be 1.
• Increment: The increment is the value added to the seed to get the next identity value. if the increment is 1, each subsequent identity value will be incremented by 1.

Adding IDENTITY to an Existing Column

If the table is created and we want to add IDENTITY to an existing column, it’s not supported.

Suppose we have a table employees created without the IDENTITY property on the id column:

Query:

CREATE TABLE employees 
(
  id int NOT NULL,
  first_name varchar(20),
  last_name varchar(30)
);

Now, if we try to modify the existing column id to add the IDENTITY property directly.

Query:

ALTER TABLE employees
ALTER COLUMN id INT IDENTITY(1,1);

Explanation: when we will run this it would result in an error similar to Incorrect syntax near ‘IDENTITY’. so, when we define an identity column, it must be done at the time of the column creation. Once the table is created, we cannot directly alter a column to become an identity column.

However, there are a few ways around this:

• Drop the column, recreate it with identity on.
• Or create a new table with identity column and drop the old table, then rename the table.

Assuming we have a table products:

Query:

CREATE TABLE products (
  product_id INT NOT NULL,
  product_name VARCHAR(50),
  price DECIMAL(10, 2)
);Drop the column and recreate it:

Now, we want to add an identity column to product_id. Here are the steps

1. Drop the Column and Recreate it

First, drop the column id without IDENTITY.

Query:

//Dropping the existing column
ALTER TABLE products
DROP COLUMN product_id;

Add the new column with IDENTITY.

Query:

ALTER TABLE products
ADD product_id INT IDENTITY(1, 1) NOT NULL;

2. Recreate the Table

Create a new table with the IDENTITY column

CREATE TABLE products_temp
(
  product_id INT IDENTITY(1, 1),
  product_name VARCHAR(50),
  price DECIMAL(10, 2)
);

Move data to the new table using SWITCH

ALTER TABLE products
SWITCH TO products_temp;

or move data using INSERT.

Query:

INSERT INTO products_temp (product_id, product_name, price)
SELECT product_id, product_name, price
FROM products;

Drop the old table.

Query:

DROP TABLE products;

Rename the new table.

Query:

EXEC sp_rename 'products_temp', 'products';

Update the IDENTITY seed if necessary.”DBCC CHECKIDENT(‘products‘);”

Now, the products table has the product_id column with the IDENTITY property.

After inserting these values in the table.

Query:

INSERT INTO products (product_name, price)
VALUES 
  ('Product A', 19.99),
  ('Product B', 29.99),
  ('Product C', 39.99);

SELECT * FROM products;

Output:

product_id	product_name	price
1	Product A	19.99
2	Product B	29.99
3	Product C	39.99

Explanation: The product_id values are automatically generated and incremented due to the identity property, starting from the specified seed value of 1 and incrementing by 1 for each new record.

EXAMPLE 2

Query:

// Original table without identity
CREATE TABLE customers 
(
  customer_id INT NOT NULL,
  customer_name VARCHAR(50),
  email VARCHAR(50)
);

//Add  sample data
INSERT INTO customers (customer_id, customer_name, email)
VALUES
  (1, 'John', 'john@example.com'),
  (2, 'rohan', rohan@example.com');

// Display 
SELECT * FROM customers;

// Drop and recreate the customer_id column with identity
ALTER TABLE customers
DROP COLUMN customer_id;

ALTER TABLE customers
ADD customer_id INT IDENTITY(1, 1) NOT NULL;

//Display the updated table with the identity column
SELECT * FROM customers;

//Add a new record
INSERT INTO customers (customer_name, email)
VALUES ('maya', 'maya@example.com');

SELECT * FROM customers;

Explanation:

• The first table we build, customers, does not have an identity column.
• Display the initial data and add some data into it.
• Recreate the customer_id column using the identity property and remove the old one.
• Display the updated table with the identity column.
• To see the identity values automatically generated, insert a new record.
• display the data from the updated table.

Output:

customer_id	customer_name	email
1	john	john@example.com
2	rohan	rohan@example.com
3	maya	maya@example.com

Explanation:

• The customer_id column has been recreated with the IDENTITY property in the updated table.
  
• With every new entry, the customer_id values are generated automatically and increased.
• the customer_id value of 3 has been assigned to the new record (“maya“).

CONCLUSION

Identity column is a great way to enable an auto increment in the table but try to make the changes before creating the table as adding it post creation of the table becomes a tedious task, when there are millions of records present in the table and try not to set the Identity column as the Primary key as users will not be able to add an identity to that column once the table has been created due to the Primary Key limitation.