sql - Setting an identity column based on a field changed value -

i have table used identity primary key (t_head_id), key became long way upsets client, changed normal column select max + 1 , added new identity column (id) , 1 computed (id_per_year) id_per_year=id+'-' year(opeartion_date)

the prob want reset id not @ beginning of year when client starts inserting new values of operation_date, since new year can started client still inserting records of passed year

how can reset identity based on changing value of operation_date?

you define column of type int (or smallint, tinyint, bigint) identity attribute:

create table dbo.yourtable( id int identity(1,1) ...... 

with setup, sql server automatically generate consecutive id's table when rows being inserted table.

with type int, starting @ 1, over 2 billion possible rows - should more sufficient vast majority of cases. bigint, 922 quadrillion (922 15 zeros - 9'220'000 billions) - enough you??

if use int identity starting @ 1, , insert row every second, need 66.5 years before hit 2 billion limit ....

if use bigint identity starting @ 1, , insert 1 thousand rows every second, need mind-boggling 292 million years before hit 922 quadrillion limit ....

read more (with options there are) in msdn books online.

so really need change id column?? if you're getting close hitting int limit (which doubt....), change bigint identity - don't start using stuff select max() + 1 inherently unsafe , will break under load , produce duplicate values - safe grief, stick identity!

doesn't 292 million years sound enough you???