WHY this SQL Fails

Well. Thanks you Shuja Ali.

I am adding a new column RateIndex by executing the SQL

ALTER TABLE VoteScore ADD RateIndex int Identity(1,1);

However, It seems the execution does not run out and log file still increases very fast. It's really weird, By the way, can we limit the log file size ? It's dangerous to run out of the disk space.

wxuf

Those SQLs I wrote executes perfectly in small tables. Do you think what the problem could be.

Yes, Thanks again.

I am using another way to update the rateindex.

I group the records by movieId, then each group is relatively small(have
about 10,000 records). The rateIndex for each group is updated. However,
it seems this operation costs much time beyond my expectation: updating 1 Million records in an hour.
My codes are easy to understand.

set nocount on;

declare @movie int, @riValue int, @ri int;
declare @message varchar(100) ;

select @riValue = 1;

declare movie_cursor cursor
for select distinct movieid from kddcup.VoteScore
order by movieid

open movie_cursor;

fetch next from movie_cursor
into @movie

while @@fetch_status = 0
begin
select @message = 'updating Movie : ' + char(@movie + 48) ;
print @message;

declare rate_cursor cursor
for select rateindex from kddcup.VoteScore
where movieid = @movie
for update;

open rate_cursor;

fetch next from rate_cursor into @ri;
while @@fetch_status = 0
begin
update kddcup.VoteScore set rateindex = @riValue
where current of rate_cursor;
select @riValue = @riValue + 1 ;
fetch next from rate_cursor into @ri;
end;

close rate_cursor;
deallocate rate_cursor;

fetch next from movie_cursor
into @movie;
end;

select @message = 'Operation Finished!';
print @message;

close movie_cursor;
deallocate movie_cursor;