How to Add Identity to Existing Column in SQL Server

Identity columns are essential in maintaining data integrity and helping you develop the ability to perform operations with data efficiently under database management.

Therefore, when you add identity to existing column in SQL Server, it will allow smooth data operation and high system performance.

This SQL Server tutorial explains the concept of identity in a column, step-by-step, relevant to confirmed issues of applying identity to an actual existing column of a table inside a server.

Add Identity to Existing Column in SQL Server

First, you must understand an identity column. An identity column in SQL Server is specialized to automatically generate unique numerical values for each new row added to a table.

Most of the time, these are surrogate or primary keys so that identification and retrieval of specific records within a table are easy. For this reason, columns with identity can be used widely for unique identification in transactional databases or even when applying data warehousing solutions.

Why Is an Identity Column Required in SQL Server?

There are several reasons for using identity columns in database design:

  • Uniqueness: An identity column ensures that all rows in a table are different. Avoiding unrepeatable entries and preserving and safeguarding data integrity is always possible.
  • Simplicity: Identity columns automatically create unique identifiers, easing the user’s task of creating one while inserting new records into a table. They also improve database performance by increasing the overhead for managing primary key records and indexing.
  • Normalization: Since identity columns form the primary key in normalized databases, joining enables an optimum pick from many tables with desired operations.

Now that we understand how identity columns work, let’s see how to add this to a column in SQL Server.

Adding an Identity Column to an Existing Column

To add identity to an existing column in SQL Server, you can use the ALTER TABLE statement and the ADD clause. Below is the syntax for adding identity to an existing column:

ALTER TABLE table_name
ADD column_name data_type
IDENTITY(seed, increment)

Where,

  • table_name: The table name we want to set an identity on the column.
  • column_name: Name of some existing column.
  • data_type: Data type of the current column.
  • seed: Value of the identity column seed.
  • increment: The incremental amount by which the identity column is increased.

Consider an example: suppose we already have the table EMPLOYEE with the following structure.

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    LastName VARCHAR(50),
    FirstName VARCHAR(50),
);

Now, let’s add in an Identity for the EmployeeID column.

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

The above example will add a new identity column EmployeeID_new to the Employee table, seed 1, and increment by 1.

An identity column will be automatically added to the Employee table after running the SQL query above. Therefore, each new row inserted will now have an identifier in the EmployeeID_new column.

Conclusion

All the above points confirm that adding identity to an existing column in SQL Server will significantly enhance databases and data integrity. Performance will even be improved once and for all.

You may like to read: