Auto Generate AlphaNumeric ID’s in a SQL Server Table

I was recently working on a query to auto generate a Column ID based on an IDENTITY column. I used computed columns to achieve this. If you too have a similar requirement, then here’s the query.

DECLARE @TT TABLE (
CandID as 'C-' + RIGHT('000' + CONVERT(varchar, CourseID),5),
CourseID int IDENTITY(1,1),
ReferrerName varchar(10)
)

INSERT INTO @TT VALUES ('Dhiraj')
INSERT INTO @TT VALUES ('Jack')
INSERT INTO @TT VALUES ('Kelly')
INSERT INTO @TT VALUES ('Kelly')
INSERT INTO @TT VALUES ('Jack')
INSERT INTO @TT VALUES ('Dhiraj')

SELECT * FROM @TT
OUTPUT



Observe how the values of the CandID column are autogenerated based on the values if the CourseID column.