How about this string keys are to be avoided (not least because they become foreign keys), also the indexes will be larger and less efficient than integers. This method uses an identity key and a derived column that calculates the invoice id from the identity column. An alternative would be to calculate the invoice id using the function and storing it alongside the identity column, might be worth storing and indexing the year too as likely to group indexes by year, can use check constraints to ensure values are in sync.
create table tbl(id int identity(1,1), dt datetime)
go
create function dbo.GetInvoiceId(@i int, @yr int) returns varchar(10)
as
begin
declare @r varchar(10)
set @r = @i - coalesce((select max(id) from tbl where year(dt) < @yr),0)
return substring(cast(@yr as varchar(4)),3,2) + substring('-000000',1, 7 - len(@r)) + @r
end
go
begin
alter table tbl add MyId as dbo.GetInvoiceId([id], year(dt))
insert tbl(dt)
select top 100 '2004-01-01' from sysobjects
insert tbl(dt)
select top 100 '2005-01-01' from sysobjects
insert tbl(dt)
select top 100 '2006-01-01' from sysobjects
select * from tbl
end
go
drop table tbl
go
drop function dbo.GetId
Phil Nicholas