второй ответ:
declare @i int
set @i=1
update [tbl] set range_id=@i,@i=@i+case when isnull([tbl].number-(select max(t.number) from [tbl] as t where t.number<[tbl].number),1)>1 then 1 else 0 end where number in (select top 100000 number from [tbl] order by number)
select * from [tbl]
или без сортировки
update [tbl] set range_id=@i,@i=@i+case when isnull([tbl].number-(select max(t.number) from [tbl] as t where t.number<[tbl].number),1)>1 then 1 else 0 end