Auto Generate AlphaNumeric ID’s in a SQL Server Table

If you wants to auto generate a Column ID based on an IDENTITY column, then here’s the query.

DECLARE @TT TABLE (CandID as 'S-' + RIGHT('000' + CONVERT(varchar, CourseID),5),CourseID int IDENTITY(1,1),ReferrerName varchar(10))INSERT INTO @TT VALUES ('Nile')INSERT INTO @TT VALUES ('Mani')INSERT INTO @TT VALUES ('Aja')INSERT INTO @TT VALUES ('Nile')SELECT * FROM @TT

OUTPUT

SlNo	CardID	CourseID	ReferenceName
1	S-0001	1	Nile
2	S-0002	2	Mani
3	S-0003	3	Aja
4	S-0004	4	Nile

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