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