How to add IDENTITY to a table in SQL Server?

In SQL Server, you 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.

Here is the syntax:

IDENTITY [ (seed , increment) ]

You must specify both seed and increment values in which:

seed is the value that is used for the very first row loaded into the table.
increment is the incremental value that is added to the identity value of the previous row that was loaded.

1. Using IDENTITY when creating a table

It’s easy to add IDENTITY to a table when creating it using the CREATE TABLE command.

Example:

We have table employees with IDENTITY specified on column id:

CREATE TABLE employees (
  id int IDENTITY (1, 1),
  first_name varchar(20),
  last_name varchar(30)
);

2. The table is created without IDENTITY column.

If the table is created and doesn’t have an IDENTITY, you can add a new IDENTITY column using the ALTER TABLE command.

For example, we have table employees without the id column:

CREATE TABLE employees (
  first_name varchar(20),
  last_name varchar(30)
);

Now add the id column with IDENTITY:

ALTER TABLE
  employees
ADD
  id int IDENTITY(1, 1) NOT NULL;

3. Add IDENTITY to an existing column

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

For example, we have table employees without IDENTITY but the id column alread existed:

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

So after the table creation, you can’t use IDENTITY when modifying the existing column id in the ALTER TABLE command. But there are some workarounds for 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.

a. Drop the column and recreate it

First, drop the column id without IDENTITY:

ALTER TABLE employees DROP COLUMN id;

Then add the new column id with IDENTITY:

ALTER TABLE
  employees
ADD
  id int IDENTITY(1, 1) NOT NULL;

b. Recreate the table

First, create a new table with the same structure, plus the IDENTITY column:

CREATE TABLE employees_temp (
  id int IDENTITY (1, 1),
  first_name varchar(20),
  last_name varchar(30)
);

Then use ALTER TABLE … SWITCH to move data to the new table:

ALTER TABLE employees SWITCH TO employees_temp;

You can also move data using the INSERT command:

IF EXISTS ( SELECT  *
            FROM    employees ) 
    INSERT  INTO employees_temp ( id, first_name, last_name )
            SELECT  id,
                    first_name,
                    last_name
            FROM    employees TABLOCKX;

Then DROP the old table:

DROP TABLE employees;

Rename the new table:

EXEC sp_rename 'employees_temp','employees';

Update the IDENTITY seed:

DBCC CHECKIDENT('employees');

And that’s done, now you have the table employees with IDENTITY specified on the id column.

4. Add an extra IDENTITY column

On a table has an IDENTITY column, if you want to an another IDENTITY column, is also not possible because there can be only one identity column in a table. If you try to do it with the ALTER TABLE command, SQL Server will throw back this error:

Multiple identity columns specified for table 'employees'. Only one identity column per table is allowed.

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.