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

I am a Software Engineer and passionate programmer. Love working in SQL Server. Like participating in online technical communities like asp.net, MSDN, technical discussions and helping my friends, colleagues and others in their technical problems

Share this

Related Posts

Previous
Next Post »

7 comments

comments
April 15, 2013 at 11:45 AM delete

dear sir,

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

Reply
avatar
April 15, 2013 at 11:49 AM delete

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

Reply
avatar
April 15, 2013 at 12:23 PM delete

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

Reply
avatar
April 15, 2013 at 12:28 PM delete

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
)

Reply
avatar
April 15, 2013 at 1:03 PM delete

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.

Reply
avatar
April 15, 2013 at 3:19 PM delete

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

Reply
avatar