Custom Primary Key

Not sure where to post this as it may be a form operation but here goes...

I want to generate a custom unique identifier for a table similar to the Northwind Customers database (ie ALFKI, ANTON, etc...) which can be used as a descriptive identifier as well.

For example, I have a Products table and I want to have my keys look like: CHR-0001-05.

"CHR" is my abbreviation for Chairs
"0001" is the auto-incrementing number (in this case the first record of the table)
"05" is the last 2 digits of the year recieved (2005)

Can somebody please point me in the right direction? I'm having problems finding resources on the web relating to this topic (prolly not using the correct keywords for my searches)

Cheers =)

You can use '+' operator to concatinate strings, here is a sample script:

if object_id('tbl_GPK','U') is not null
drop table tbl_GPK
go
Create table tbl_GPK (EName varchar(50),EDate smalldatetime)
go
insert into tbl_GPK select 'Peacock Margaret',GetDate()

if object_id('UDF_Gen_PK','FN') is not null
drop function UDF_Gen_PK
go
create function UDF_Gen_PK (@.S varchar(50))
returns varchar(60)
as
begin
declare @.outstring varchar(60)
declare @.i int
select @.i=1,@.outstring=''
while (@.i<=len(@.S))
begin
select @.outstring=@.outstring+substring(@.S,@.i,1)
set @.i=@.i+5
end
select @.outstring=@.outstring+'-'+convert(varchar(10),count(*)+1)
from tbl_GPK
return @.outstring
end

go
alter table tbl_GPK
add PK as (dbo.UDF_Gen_PK(EName)+'-'+convert(varchar(4),Year(EDate)))
go

select * from tbl_GPK