Auto Increment Custom ID (Sequence) in Sql server

In Sql server we can set the property "Auto Identity=true" in a database table so that the integer column will be automatically incremented.

There are cases where we need such a auto increment ID but it shouldn't be an integer.

Let's say we need the below sequence to be generated.

EMP001 

EMP002

EMP003
......

To get the above out put you can simply use the below code.

CREATE TABLE EMPLOYEE ( 

 RealID int NOT NULL IDENTITY (1, 1),

 EMP_NAME ...

 ... 

 /*gives 000-999. Change the RIGHT as needed to give more*/ 

 EMP_ID AS 'EMP' + RIGHT('000000000' + CAST(RealID as varchar(10)), 3) 

CONSTRAINT PK_EMPLOYEE PRIMARY KEY CLUSTERED (EMP_ID) 

 )

You can change the RIGHT to cover as many digits as needed, or you may not want leading zeroes: EMP_ID AS 'EMP' + CAST(RealID as varchar(10))