Use the autoincrement feature but just extract the extra info when you query the database ....
CREATE TABLE #chill60
(
ID int IDENTITY(1,1),
TXT varchar(20)
)
insert into #chill60 select * from
(select top 10 (char(abs(checksum(NEWID())) % 26 + 65) + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)) AS TXT
FROM syscolumns ac1 cross join syscolumns ac2 cross join syscolumns ac3) as a
Then use something like this to get the data back in the format you want
SELECT 'emp'+replace(str(ID, 7), ' ', '0') as empid, TXT
FROM #chill60
Returns
empid TXT
emp0000001 VD
emp0000002 AI
emp0000003 AS
emp0000004 EB
emp0000005 XU
emp0000006 UR
emp0000007 IW
emp0000008 IA
emp0000009 YS
emp0000010 US
You could put the 'emp'+replace(str(ID,7), ' ','0') bit into a function or even have a database trigger that populates a-n-other column with this derived value based on the auto-incremented ID. There are advantages to using built-in functionality rather than trying to manage the IDs yourself
[EDIT - with acknowledgements to Rob Bowman for the quick generation of test data
http://geekswithblogs.net/RobBowman/archive/2011/10/11/generate-sql-server-test-data.aspx[
^]