Auto generate auto incremented unique alphanumeric id or number in sql server

Introduction: In this article I am going to share how to auto generate unique auto incremented alphanumeric code or number and store in sql server table while inserting any record.

In previous articles i explained What are Temporary tables, their types and examples of use and Insert multiple records in table in single insert statement and Backup and restore sql server database and CTE to find and remove duplicate records from table and 20 main differences between stored procedures and user defined functions 

Description:  While working on project I got the requirement to auto generate 8 character employee code having 3 characters "EMP" as a prefix and 5 digits number and it should be auto incremented whenever a new record in inserted in the table.  

As we know primary key column of a table is always unique so we can use that column value to auto generate employee code. I made the "EmpCode" column a computed column and specified the formula to generate the auto incremented alphanumeric number.  Computed columns are derived columns based on other existing columns in the same table or we can say a computed column is computed from an expression/formula that can use another column or columns in the same table.

Implementation: Let’s generate alphanumeric employee code automatically.

CREATE TABLE tbEmployee

(

                EmpId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

                EmpCode AS 'EMP' + RIGHT('0000'+ CONVERT(VARCHAR(5),EmpId),5) PERSISTED,

                EmployeeName VARCHAR(50),

                Age        INT,

                Gender VARCHAR(10)

)

--INSERT RECORDS IN TABLE

INSERT INTO tbEmployee (EmployeeName,Age,Gender)VALUES

('Mayank',25,'Male'),

('Ankita',24,'Female'),

('Anuj',26,'Male'),

('Jaswinder',25,'Male'),

('Simran',25,'Female') 

--CHECK INSERTED RECORD

SELECT * FROM tbEmployee

Result will be: 

EmpId	EmpCode	EmployeeName	Age	Gender
1	EMP00001	Mayank	25	Male
2	EMP00002	Ankita	24	Female
3	EMP00003	Anuj	26	Male
4	EMP00004	Jaswinder	25	Male
5	EMP00005	Simran	25	Female

  Explanation: When the very first employee record is saved in the table, the value of primary key column "EmpId" will be 1 so the computed column "EmpCode" will be auto generated as EMP00001 and auto stored in table, similarly when 105^th employee record is saved, "EmpCode" will be generated as EMP00105, for 1580^th record it will be EMP01580 and for 10000^th record it will generate EMP10000.

To test whether it actually works or not,execute the following script 

--CREATE A TEMPORARY TABLE

CREATE TABLE #Temp

(

                Id INT NOT NULL IDENTITY(1,1),

                AutoId AS 'ABC' + RIGHT('0000'+ CONVERT(VARCHAR(5),Id),5)

)

--INSERT DEFAULT 10000 RECORDS AT ONCE

INSERT INTO #Temp DEFAULT VALUES;

GO 10000

--CHECK INSERTED RECORD

SELECT * FROM #Temp

Result will be as:

--DROP TEMPORARY TABLE

DROP TABLE #Temp

Now over to you:

"A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates." 

• Tweet
• Share
• Share
• Share

Related Post