SQL Server identity counterpart problem

Question

Hi there,

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

Answer 1

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)

Answer 2

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?