How To Add Identity To SQL Server Table Column

An identity column is a column for which a value is generated automatically when a new record is inserted into a table. Identity function can only be applied on integer type column.

An identity column can be created along with a primary key field which generates a unique and auto-increment value for every new inserted record.

In SQL Server we use the IDENTITY keyword to perform an auto-increment feature. By default the IDENTITY function has a starting value of 1 and is incremented by 1.

But, We can mention the IDENTITY starting and incremental value as IDENTITY(starting Value, Incremental Value)

For example, IDENTITY(10,5) will start the column value from 10 and increment it by 5 for every new record.

IDENTITY Function on CREATE TABLE

CREATE TABLE tblEmployee
(
    EmpID INT IDENTITY(1,1) PRIMARY KEY,
    EmpFirstName NVARCHAR(20),
    EmpLastName NVARCHAR(20),
    EmpAge INT,
    EmpGender NVARCHAR(10),
    EmpEmail NVARCHAR(50),
    EmpPhone NVARCHAR(20),
    EmpSalary INT,
    EmpJoiningDate DATETIME
)
GO

In the above table tblEmployee, EmpID column is PRIMARY KEY as well as IDENTITY(1,1) Column, where in (1,1), first 1 is for the starting number and the second 1 is for the incremental number. Therefore, there is no need to provide value for EmpID explicitly. It will generate the values starting from 1 and auto increase it by 1 for every new record. Let’s see with an example.

INSERT INTO tblEmployee
(
    EmpFirstName, EmpLastName, EmpAge, EmpGender, EmpEmail, EmpPhone, EmpSalary, EmpJoiningDate
)
VALUES
    ('john','dave',26,'male','john@gmail.com','0923044554561',26000,'2010-02-01'),
    ('kamran','akmal',35,'male','kami@gmail.com','0923044545562',25000,'2011-01-01'),
    ('umar','akmal',36,'male','umar@gmail.com','0923044554563',38000,'2015-01-01'),
    ('simran','sidhu',25,'female','simran@gmail.com','0923044554565',33000,'2018-01-01'),
    ('diyana','jeni',26,'female','diyana@gmail.com','0923044554566',50000,'2005-01-01'),
    ('maria','anders',27,'female','maria@gmail.com','0923044554567',70000,'2004-01-01')
GO

We can see in the above example that, I inserted five rows without providing the value for EmpID column, which is identity column. EmpID column generated 1, 2, 3, 4, 5 and 6 for row 1, row 2, row 3, row 4, row 5 and row 6 respectively as shown in the following image.