create table #t (i tinyint identity, seq tinyint)
create nonclustered index ix_seq on #t(seq)
go
insert #t values(1)
insert #t values(2)
insert #t values(3)
delete #t where i = 3
insert #t values(5)
insert #t values(4)
insert #t values(6)

select * from #t order by seq

update #t
set seq = ( select count(*) from #t as t1
             where t1.seq <= #t.seq )
select * from #t order by seq
go
truncate table #t
go
insert #t values(1)
insert #t values(2)
insert #t values(3)
delete #t where i = 3
insert #t values(5)
insert #t values(4)
insert #t values(6)
go
update #t
set seq = ( select count(*) from #t as t1
             where t1.seq <= #t.seq )
select * from #t order by seq
go
drop table #t
go

This page was last updated on May 01, 2006 04:28 PM.