Creating autogenerated sequence in SQL

Hi Guys,

Sometimes we may have to create a auto generated sequenced during development. We can do this in application language by creating a function that will check current value fro table in database and then will generate the next sequence as well as directly in database.

Below is the query that I have written some days ago while I faced the same situation.

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 :

Is it not good? It will just reduce the coding at front end and empower that database from back end thus reducing the application overhead to generate the next sequence.

Comments and suggestions are always welcome.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s