MS SQL server: How to dynamically number rows in a SELECT T-SQL statement

set nocount on
if exists (select * from sysobjects where type = 'U' and  name = 'ranked_table')

begin
 drop table ranked_table
end

-- create a test table (just for example)
create table ranked_table
(
 id_test int identity not null,
 string varchar (255),
 constraint pk_test primary key (id_test)
)


set identity_insert ranked_table on

-- insert sample data
insert into ranked_table (id_test, string)
values (1,'testdata1')

insert into ranked_table (id_test, string)
values (3,'testdata2')
insert into ranked_table (id_test, string)

values (11,'testdata3')
insert into ranked_table (id_test, string)
values (45,'testdata4')

insert into ranked_table (id_test, string)
values (58,'testdata5')
insert into ranked_table (id_test, string)

values (61,'testdata6')
insert into ranked_table (id_test, string)
values (111,'testdata7')

insert into ranked_table (id_test, string)
values (123,'testdata8')
insert into ranked_table (id_test, string)

values (134,'testdata9')

set identity_insert ranked_table off
go

-- 1. Create a View
if exists (select * from sysobjects where type = 'V' and name = 'ranked_view')

begin
 drop view ranked_view
end
go
create view ranked_view (rank, id_test, sting)

as
 select (
   select count(*)
   from ranked_table as test_2
   where test_2.id_test <= test_1.id_test
  ) as rank,
  test_1.id_test,
  test_1.string
 from ranked_table as test_1

go
select *
from ranked_view
order by rank
go


-- 2. "Standard" T-SQL
select count (test_2.id_test) as rank, test_1.id_test, test_1.string
from ranked_table as test_1 inner join ranked_table 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. "Standard" T-SQL
select test_3.rank, test_3.id_test, test_3.string
from (select test_1.id_test,
  test_1.string,
  (select count(*)
   from ranked_table as test_2
   where test_2.id_test <= test_1.id_test
  ) as rank
 from ranked_table as test_1) as test_3

order by rank
go

-- 4. Temporary table with IDENTITY
if not object_id('tempdb..#rank_table') is null
begin
 drop table #rank_table
end
create table #rank_table
(
 rank int identity not null,
 id_test int null,
 string varchar (255),
 constraint pk_test primary key (rank)
)
go
insert into #rank_table (id_test, string)
select id_test, string
from ranked_table
order by id_test

select * from #rank_table
go

-- 5. Table variable with IDENTITY
declare @rank_table table
(
 rank int identity not null,
 id_test int null,
 string varchar (255)
)

insert into @rank_table (id_test, string)
select id_test, string
from ranked_table
order by id_test
select * from @rank_table

go

-- 6. Cursor
declare @rank int,
 @id_test int,
 @string varchar (255)

declare rank_cursor cursor
for select id_test, string
 from ranked_table
 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