Wednesday, April 18, 2012

auto generate employee id

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
OUTPUT

7 comments:

  1. dear sir,

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

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

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

    ReplyDelete
    Replies
    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
      )

      Delete
  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.

      Delete
    2. Also, refer this link to learn more about computed columns
      http://itdeveloperzone.blogspot.in/2013/04/computed-column-in-sql-server.html

      Delete