Нумерация строк запроса

use pubs
 
set nocount on
if exists (select * from sysobjects where type = 'U' and  name = 'test')
begin
    drop table test
end
/* создание таблицы для примера */
create table test
(
    id_test int identity not null,
    string char (7),
    constraint pk_test primary key (id_test)
)
/* установка флага для занесения с определенными ид. */
set identity_insert test on
/* занесение тестовых значений с произвольными ид. */
insert into test (id_test, string)
values (1,'string1')
insert into test (id_test, string)
values (4,'string2')
insert into test (id_test, string)
values (12,'string3')
insert into test (id_test, string)
values (17,'string4')
insert into test (id_test, string)
values (29,'string5')
insert into test (id_test, string)
values (31,'string6')
insert into test (id_test, string)
values (42,'string7')
insert into test (id_test, string)
values (45,'string8')
insert into test (id_test, string)
values (61,'string9')
/* отмена установки флага для занесения с определенными ид. */
set identity_insert test off
go
/* способ №1, создание проекции. */
if exists (select * from sysobjects where type = 'V' and name = 'ranked_table')
begin
    drop view ranked_table
end
go
create view ranked_table (rank, id_test, sting)
as
    select (
            select count(*) 
            from test as test_2
            where test_2.id_test <= test_1.id_test
        ) as rank,
        test_1.id_test,
        test_1.string
    from test as test_1
go
select * 
from ranked_table
order by rank
go
/* способ №2 стандартный SQL */
select count (test_2.id_test) as rank, test_1.id_test, test_1.string
from test as test_1 inner join test as test_2 on
    test_1.id_test >= test_2.id_test
group by test_1.id_test, test_1.string
order by rank
go
/* способ №3 стандартный SQL */
select test_3.rank, test_3.id_test, test_3.string
from (select test_1.id_test,
        test_1.string,
        (select count(*) 
            from test as test_2
            where test_2.id_test <= test_1.id_test
        ) as rank
    from test as test_1) as test_3
order by rank
go
/* способ №4, временная таблица с полем identity */
create table #rank_table
(
    rank int identity not null,
    id_test int null,
    string char (7),
    constraint pk_test primary key (rank)
)
go
insert into #rank_table (id_test, string)
select id_test, string
from test
order by id_test
select * from #rank_table
go
/* способ №5, переменная типа table с полем identity */
declare @rank_table table
(
    rank int identity not null,
    id_test int null,
    string char (7)
)
insert into @rank_table (id_test, string)
select id_test, string
from test
order by id_test
select * from @rank_table
go
/* способ №6, курсор */
declare @rank int,
    @id_test int,
    @string char (7)
declare rank_cursor cursor
for select id_test, string
    from test
    order by id_test
open rank_cursor
fetch next from rank_cursor into @id_test, @string
set @rank = 1
while (@@fetch_status <> -1)
begin
    select @rank, @id_test, @string
    set @rank = @rank + 1
    fetch next from rank_cursor into @id_test, @string
end
close rank_cursor
deallocate rank_cursor

declare i int
set i=0
 
select 0 as id, Name, Comm
into #temp
from MyTable
 
update #temp set @i=id=@i+1
 
select * from #temp
 
drop table #temp