Method #1
you can not auto incr varchar column
make a integer column and set it autoincr while you want to display,
use query as below...
SELECT 'refid' + RIGHT(REPLICATE(0,4-len(Id)) + convert(varchar,Id) from tblnm
------------------------------OR------------------------------
Method #2
Create functions that can increment varchar with 1
write query
insert into tbl(id) select (select dbo.incr(max(id)) from tbl)
select dbo.incr('refid0002')
create function
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ParseValues]
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(50))
AS
BEGIN
DECLARE @Value varchar(100)
WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END
Create this function also
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Incr]
(
@a varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
select @a=val from [dbo].[ParseValues](@a,'refid')
select @a= convert(varchar,(convert(numeric,@a)+1))
select @a= 'refId' + replicate('0',4-len(@a)) + @a
return @a
END
Happy Coding!
:)