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
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 tblEmployeeRESULT