asked
Changing the Identity seed every year automatically .
In my current system the identity field has 20120280 as the initially 4 numbers being the year..now is there any possibility that the this automatically detects the year and changes ? > in 2012 - 20120280 in 2013 - > 20131250 etc ..
At the start of the new year, run a scheduled job that reseeds the identity on your table using DBCC checkident ("YourTable", reseed, 20130000) assuming of course that the identity value hasn't rolled over from 20129999 If you wanted the last 4 digits to stay in sequence then some additional work would be need to get the current value, swap out the first four digits and then use that as the reseed value : declare @MaxID int declare @ReseedValue int set @MaxID = (select max(identitycolumn) from YourTable) set @ReseedValue = cast('2013' + right(cast(@MaxID as varchar),4) as bigint) dbcc checkident("YourTable", reseed, @ReseedValue)
you also want to consider whether you want 20130000 or 20130001 as the starting ID. The number you specify in the DBCC CHECKIDENT is not used for the first insert. Unlikely you will want 20130000 but thought I'd mention it!
@Fatherjack +1. Nice catch... @fashraf How this construct is serving the purpose.... I don't wanna think of ;-) But the caveats seems be too much to put it into production... Anyways, best of luck for the future.
6 People are following this question.
