обновление sorted by выборки

[Vadik]

Цитата:

Сообщение от SANTAN

У меня на 2005 не получается...

2005 под рукой нет

set nocount on
go

if object_id('tempdb..#t') is not null drop table #t
go

create table #t ([id] int not null identity primary key nonclustered, [value] int not null)
go

declare @i int 
set @i = 0
while @i < 10000 begin
	set @i = @i + 1
	insert into #t ([value]) select rand() * 1000
end
go

create index IX_T_Value on #t([value])
go

declare @id int, @value int, @cnt int

declare cr cursor dynamic for select [id], [value] from #t order by [value]

set @cnt = 0

open cr
fetch next from cr into @id, @value
while @@fetch_status = 0 begin
	set @cnt = @cnt + 1

	print 'Итерация ' + convert(varchar, @cnt) + ', id=' + convert(varchar, @id) + ', value = ' + convert(varchar, @value)

	update #t set [value] = [value] + rand() * 1000 - 500 where [id] = @id

	fetch next from cr into @id, @value
end

close cr
deallocate cr
go

drop table #t
go

сравните количество итераций
- без создания некластерного индекса
- с созданным индексом