How to reset sql identity value per year?

Hi' I'm working with an asp.net application wich related to the registration of students, the data related to the students is inserted in to a database, each time a student is registered the field idStudent is incremented by one, what I want to do is when a new year begins reset the student value in order to begin from 1 in the new year.

• idStudent Year 1 2011 2 2011 3

  2011 4 2011 5 2011 ..... 1 2012 开发者_如何学Go2

  2012 3 2012 .......

How can I do this? The databse is in sql server 2008

Hope your help

If I had this exact business requirement and I couldn't negotiate a better, more efficient way then this would be my approach:

Instead of using an artificial key (i.e. an identity column) I would utilize a composite key. To find out what your best bet for a composite key would be, you need to know the business rules and logic. In other words, a question you would have to ask is is the combination of year and id unique? In other words, per year a student id can only be used once...?

This is one of those times were you would benefit from a natural composite key.

You could add a computed column which derives the number based on the year. You just need to run the script below at the start of the new year. Do note that when a transaction is rolled back there will be gaps in the identity column. (and inserts in a previous year will create big gaps as well.)

-- test table & inserts
create table Students (Id int identity, year int, name varchar(20), StudentId int null)
insert into Students (year, name) values (2010, 'student1'), (2011, 'student1'), (2011, 'student2')

-- run this every year
EXEC ('ALTER TABLE Students DROP COLUMN StudentId ')
declare @count int, @sql varchar(max), @year int

declare c cursor local read_only 
for select year + 1, max(Id) from Students group by year

open c

fetch next from c into @year, @count
select @sql = 'when year = ' + convert(varchar(10), @year - 1) + ' then Id '+ CHAR(13) + CHAR(10)
while @@FETCH_STATUS = 0 
  begin
    select @sql = @sql + 'when year = ' + convert(varchar(10), @year) + ' then Id - ' + convert(varchar(10), @count) + CHAR(13) + CHAR(10)
    fetch next from c into @year, @count
  end
close c
deallocate c
select @sql = 'CASE ' + CHAR(13) + CHAR(10) + @sql + ' ELSE 0 END'
select @sql = 'ALTER TABLE Students ADD StudentId AS ' + isnull(@sql, 'Id') + '  PERSISTED'

exec (@sql)