Auto Generate Employee Id in SQL Server

In this post, I have explained how Employee Id can be auto generated very simply with an identity and a computed column.

Suppose, we have to generate employee id's in the format AA00001 where

• 1st character - 1st character of First Name
• 2nd character - 2st character of Last Name
• Next 5 characters - Sequential Numerical value

To generate sequential values take an identity column that would do the auto increment for you.
Now take a Computed Column to generate employee id and set the formula to generate employee id in the desired format like below.

Now you may have the requirement to make the employee id column as Primary Key. For this, make the Computed Column as Persisted, as primary key can be created on Computed Column only if the column is Persisted.

CREATE TABLE tblEmployee(
    Id INT identity
    , EmpId AS (LEFT(EmpFirtsName,1) + LEFT(EmpLastName,1)
      + REPLICATE('0', 5-LEN(Id)) + CAST(Id AS VARCHAR)) PERSISTED PRIMARY KEY
    , EmpFirtsName VARCHAR(50) NOT NULL
    , EmpLastName VARCHAR(50) NOT NULL
)

INSERT into tblEmployee(EmpFirtsName, EmpLastName) values('Ajay', 'Anand')
INSERT into tblEmployee(EmpFirtsName, EmpLastName) values('Sanjay', 'Singh')
INSERT into tblEmployee(EmpFirtsName, EmpLastName) values('Vijay', 'Kumar')

SELECT * FROM tblEmployee

RESULT