Generate AlphaNumeric Identity Column in SQL Server

Let us suppose you have a requirement where you want to have an alphanumeric identity column that has characters and numbers and you want these numbers to be incremental. Eg: Customer ids like cus0000001, cus0000002, etc. You can use any of the following methods

Method 1 : Store alphanumeric data in column

declare @t table(id char(10), names varchar(100))
insert into @t
select 'cus0000001','ramesh' union all
select 'cus0000002','suresh'

declare @id int
select @id=MAX(substring(id,4,len(id)))*1 from @t

insert into @t
select 'cus'+RIGHT(REPLICATE('0',7)+cast(@id+1 as varchar(7)),7),'nilesh'
select * from @t

In the above code, @id is assigned the maximum value of the number excluding characters "cus", which is used again in the INSERT statement

Method 2 : Use only identity column of int datatype and append alphabets in select statement

declare @t table(id int identity(1,1), names varchar(100))
insert into @t (names)
select 'ramesh' union all
select 'suresh' union all
select 'nilesh'

select 'cus'+right(replicate('0',7)+cast(id as varchar(7)),7) as id,names from @t

In the above code, an alphanumeric string is built dynamically in the select statement