How to Insert Identity Column in SQL Server

In this SQL Server tutorial, I will show you how to insert identity column in SQL Server.

Inserting the unique ID for each record in the table is not a good approach, so SQL Server provides an attribute called Identity column, which tells SQL Server to generate a unique value for each record automatically.

In this tutorial, you will learn how to insert the column with the identity attribute in your existing table to automatically insert the unique value for each record.

Additionally, you will learn about identity columns with syntax and why you should use them in your table.

Table of Contents

Insert Identity Column in SQL Server

First, you must know what the identity column is. The identity column is the column whose value is obtained automatically in a sequential manner for each record inserted into the table one by one.

But what is the purpose of the identity column, or why should we use it? The identity column generates a unique value for each row or record in the table. Thus, it helps in data management and retrieval.

Generally, the identity column acts as the primary key column of the table, uniquely identifying each record in the table.

The syntax is given below on how to define the identity column in the table.

column_name data_type [IDENTITY(seed, increment)]

Where,

column_name: It is the name of the column on which the identity attribute is applied.
data_type: Specifies the data type of the column. It can be any valid data type, such as int, bigint, smallint, tinyint, etc.
IDENTITY(seed, increment): It is the identity attribute. It is made up of two optional parameters:
- seed: This parameter specifies the starting value of the identity column. It indicates the initial value that is assigned to the first row. If not provided, the default seed value is 1.
- increment: This parameter specifies the increment value by which the identity values will increase for each new row. If not provided, the default increment value is 1.

Now, you know about the identity column and why to use it. Inserting an identity column means adding a new identity column to the existing table.

For example, create an EmployeeInfo table without the identity column using the below command.

CREATE TABLE EmployeeInfo (
    FirstName varchar(50),
    LastName varchar(50),
    Department varchar(50),
    Position varchar(50),
    Salary decimal(10, 2),
    HireDate date
);

When you execute the above command, it creates a new table named EmployeeInfo in your database with columns FirstName, LastName, Department, Position, Salary, and HireData.

Look, the table EmployeInfo does not have an identity column. Let’s insert a new one. To do that, you will need to alter the table using the ALTER TABLE command with the ADD clause.

ALTER TABLE EmployeeInfo
ADD EmployeeID INT IDENTITY(1,1) PRIMARY KEY;

Look at the above output, the ALTER TABLE command adds the identity column using the ‘ADD EmployeeID INT IDENTITY(1,1) PRIMARY KEY’ to the table EmployeeInfo.

To verify that execute the command below.

SELECT 
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = 'EmployeeInfo' 
            AND COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
        ) THEN 'Yes'
        ELSE 'No'
    END AS HasIdentityColumn,
    (
        SELECT COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = 'EmployeeInfo' 
        AND COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
    ) AS IdentityColumnName;

Checking the Existence of Identity Column After Inserting Identity Column in SQL Server

From the output, you can see that the above query returns the result set containing the two columns HasIdentityColumn and IdentityColumnName, indicating that the table has an identity column named EmployeeID.

Next, let’s insert some of the records into the table and see if the identity column is working.

INSERT INTO EmployeeInfo (FirstName, LastName, Department, Position, Salary, HireDate)
VALUES
    ('James', 'Doe', 'Engineering', 'Software Engineer', 75000.00, '2023-01-15'),
    ('John', 'Smith', 'Marketing', 'Marketing Manager', 85000.00, '2022-11-10'),
    ('Robert', 'Johnson', 'Sales', 'Sales Representative', 60000.00, '2023-03-20'),
    ('Michael', 'Williams', 'Human Resources', 'HR Specialist', 65000.00, '2023-02-05'),
    ('William', 'Brown', 'Finance', 'Financial Analyst', 70000.00, '2023-04-18'),
    ('David', 'Jones', 'Engineering', 'Senior Software Engineer', 90000.00, '2022-12-01'),
    ('Richard', 'Martinez', 'Marketing', 'Marketing Coordinator', 55000.00, '2023-05-10'),
    ('Charles', 'Garcia', 'Sales', 'Sales Manager', 95000.00, '2022-10-15'),
    ('Joseph', 'Rodriguez', 'Human Resources', 'HR Manager', 100000.00, '2022-09-20'),
    ('Thomas', 'Lopez', 'Finance', 'Finance Manager', 110000.00, '2022-08-10'),
    ('Christopher', 'Lee', 'Engineering', 'Systems Architect', 105000.00, '2023-06-15'),
    ('Daniel', 'Perez', 'Marketing', 'Digital Marketing Specialist', 60000.00, '2023-07-20'),
    ('Paul', 'Taylor', 'Sales', 'Sales Director', 120000.00, '2022-07-05'),
    ('Mark', 'King', 'Human Resources', 'Recruitment Manager', 85000.00, '2023-08-18'),
    ('Donald', 'Scott', 'Finance', 'Financial Controller', 130000.00, '2023-09-25');

SELECT * FROM EmployeeInfo;

Inserting Records After Inserting Identity Column in Table of SQL Server

From the output, look at the column EmployeeID, which contains the sequential unique value for each record in the table. We haven’t specified this column value while inserting the data; it is automatically generated because this column is an identity column.

Some of the points you must remember about the identity column.

By default, when you are going to insert data into a table with an identity column, you don’t need to include the identity column in your INSERT statement. SQL Server automatically generates the identity values for you.
In some cases, you may need to explicitly insert identity values into the table. SQL Server allows you to do this by using the SET IDENTITY_INSERT statement, but it’s crucial to use this feature judiciously to maintain data integrity.
Before explicitly inserting identity values, you must enable the table’s identity insert mode. This allows you to temporarily bypass the automatic generation of identity values.

I hope from the above example, you understand how to insert an identity column in SQL Server.

Conclusion

In this SQL Server tutorial, you learned how to insert an identity column in an SQL server using the identity attribute ‘IDENTITY(1,1)’.

Apart from this, you understand how the identity column works and why you must include it in your table. Lastly, you learned about some points related to the identity column which you must keep in your mind while inserting the identity column in the existing table.