tsql - Add Year Prefix in auto generated id which is a primary key -

tsql - Add Year Prefix in auto generated id which is a primary key -

i want id autogenerated , want format this:

12-0001, 12-0002

12 represent year 2012 current year of scheme date.

and lastly record id 12-0999 , when 2013 comes want id alter year prefix , reset 4 digit this:

13-0001, 13-0002.

i'm using asp.net mvc 3 , sql server express 2008.

can tell me of way can this.

i see 2 options:

(1) if table you're inserting info has date or datetime column has "right" year in it, add together persisted, computed column table - like:

alter table dbo.yourtable add together pkid right(cast(year(datecolumn) char(4)), 2) + '-' + right('00000' + cast(id varchar(5)), 5) persisted

assuming id int identity column autogenerates sequential numbers, , want phone call new column pkid (change needed).

since persisted computed column, it's computed 1 time - when row inserted - , can indexed , used primary key.

(2) if don't have date column in table, alternative have after insert trigger on table (again: assuming have column id int identity provide auto-incremented numbers):

create trigger trginsert on dbo.yourtable after insert begin declare @yearprefix char(2) set @yearprefix = right(cast(year(getdate()) char(4)), 2) update dbo.yourtable set otherid = @yearprefix + '-' + right('00000' + cast(i.employeeid varchar(5)), 5) inserted dbo.yourtable.employeeid = i.employeeid end

and of course, first need add together new pkid column table, too, trigger can store values in :-)

alter table dbo.yourtable add together pkid varchar(10) not null default ('x') -- needs not null primary key

tsql primary-key sql-server-2008-express