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)
精彩评论