SQL Server: Adding IDENTITY Property to an Existing Column

Adding the IDENTITY property to an existing column in SQL Server is not as straightforward as changing the column data type. SQL Server does not provide a simple ALTER COLUMN statement to add the IDENTITY property to an existing column.

One way to accomplish this is to create a new IDENTITY column, remove the old column, and then rename the new column with the original column name. The following example shows the steps.

Steps to Add the IDENTITY Property

• Add a new column with the `IDENTITY` property. 
• Drop the constraint, if any.
•  Drop the old column.
• Rename the new column with the old column name.

Existing Table

CREATE TABLE Employee
(
Emp_Id INT PRIMARY KEY,
Emp_Name VARCHAR(100)
); 

1. Add a New Column with IDENTITY

ALTER TABLE Employee
ADD EmpNew_ID INT IDENTITY(1,1);

2. Drop the Primary Key Constraint

If the existing column is a primary key, first drop the primary key constraint.

ALTER TABLE Employee
DROP CONSTRAINT PK__Employee__262359AB07020F21;

Note: The constraint name above is an example. The actual constraint name may be different in your database.

3. Drop the Old Column

ALTER TABLE Employee
DROP COLUMN Emp_Id

4. Rename the New Column
 

Rename the new column with the old column name:

EXEC sp_rename 'Employee.EmpNew_ID', 'Emp_Id', 'COLUMN';

Now the table has the new `IDENTITY` column with the original column name. 

Remarks

I came across this approach through one of my co-workers. At first, I thought it would be as simple as altering the existing column, but after looking into it, I realized that adding the `IDENTITY` property to an existing column requires a different approach.

The above method works by creating a new column with the `IDENTITY` property, removing the old column, and then renaming the new column.

Before using this approach, make sure you understand the impact on existing data and dependencies. If the table contains data, dropping the old column will remove that data. Also, consider any primary keys, foreign keys, indexes, constraints, triggers, and other objects that depend on the old column.

If necessary, drop or recreate the required constraints and dependent objects as part of the change.
 

Display the Table

SELECT *
FROM Employee;