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
 /* результат всех примеров
rank        id_test     string  
----------- ----------- ------- 
1           1           string1
2           4           string2
3           12          string3
4           17          string4
5           29          string5
6           31          string6
7           42          string7
8           45          string8
9           61          string9
 */