Apr 18, 2012

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)
    , 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

    Choose :
  • OR
  • To comment
Write Comments
  1. dear sir,

    this is good example for booking id ...
    but im not executing this query..
    pls modify and post query...

    1. Hi Dilip,
      This is a sample to auto-generate id's. You need to modify the code as per your logic & requirement.

  2. sir i need query for auto generation like boo1001,boo1002....so on

    1. Sample Table Script
      CREATE TABLE tblBooking(
      Id INT identity
      , BookingId AS ('boo' + CAST(1000 + Id AS VARCHAR)) PERSISTED PRIMARY KEY
      , BookingName VARCHAR(50) NOT NULL

  3. Replies
    1. Here, BookingId is basically a computed column. Computed columns can be persisted or non-persisted. Persisted columns are calculated at the time on inserting and updating the data and stored permanently while non-persisted are calculated on run-time each time when referred.

    2. Also, refer this link to learn more about computed columns


Probabilmente come studente universitario ti troverai a scrivere una o più tesi per il tuo analogo esame di fine carriera. Ma di cosa si tratta in particolare? Scopriamo quali sono i pro dei tool per riscrivere testo che troverai online. Per strumento di parafrasi si intende un tool online che si occupa di parafrasare in maniera immediata frasi oppure interi paragrafi. Questa attività si svolgerà interamente su internet e non lascerà alcuna traccia di plagio