Surely you are getting confused between the number value and its desired 'display' representation - '2' is correct from sql-server point of view
you dont say what version of SQL server you're using, 'newer' versions ?>2012 have a 'FORMAT' function
FORMAT (Transact-SQL)[
^] ie you might be able to do
select FORMAT(MAX(maxid)+1, 'd4') from tblcategoryuser;
If you're using < 2012, well, then you might get away with
select right('0000' + cast(MAX(maxid)+1 as varchar(4)), 4)