SQL Server identity counterpart problem

I'm using SQL Server and I want to use identity constraint in it

I know how to use it in following manner

create table mytable
(
 c1 int primary key 开发者_运维百科identity(1,1);
)

the above code works fine but what if i want the identity column to have values as EMP001, EMP002,... instead of 1,2....

Thanks in advance, Guru

Identity columns can only be integers. If you want it to "look like" EMP001, EMP002, etc then that's simply a display issue and not a storage one (that is, you don't need to store "EMP001", "EMP002" etc, just store it as 1, 2, 3 via a normal identity column and display it in your application as "EMP001", "EMP002", etc)

Create a computed column that concatenates like this:

'EMP' + RIGHT('00' + CAST(c1 AS varchar(3)), 3)

Otherwise an IDENTITY column as surrogate key is just that: a meaningless number.

I assume you're only going to have 999 rows or is there another sequence somewhere?