Four ways to add row number in SQL Server

It is easy to sort any data in SQL Server, while it is not a trivial job to add a new variable of row number to an existent table. Here I come with four ways to realize the purpose.

/********** 0. Input data source *********************/ 
use tempdb
if object_id('class') is not null 
   drop table class;
go

-- Use a table with 5 variables from 18 teenagers
create table class (name char(8), sex char(1), 
   age numeric, height numeric, weight numeric );
insert into class values('ALFRED','M',14,69,112.5);
insert into class values('ALICE','F',13,56.5,84);
insert into class values('BARBARA','F',13,65.3,98);
insert into class values('CAROL','F',14,62.8,102.5);
insert into class values('HENRY','M',14,63.5,102.5);
insert into class values('JAMES','M',12,57.3,83);
insert into class values('JANE','F',12,59.8,84.5);
insert into class values('JEFFREY','M',13,62.5,84);
insert into class values('JOHN','M',12,59,99.5);
insert into class values('JOYCE','F',11,51.3,50.5);
insert into class values('JUDY','F',14,64.3,90);
insert into class values('LOUISE','F',12,56.3,77);
insert into class values('MARY','F',15,66.5,112);
insert into class values('PHILIP','M',16,72,150);
insert into class values('ROBERT','M',12,64.8,128);
insert into class values('RONALD','M',15,67,133);
insert into class values('THOMAS','M',11,57.5,85);
insert into class values('WILLIAM','M',15,66.5,112);

/********** 1. Primary key with auto-increment *********************/ 
create table class2 (row_num smallint identity(1,1) primary key clustered, 
   name char(8), sex char(1), age numeric, height numeric, weight numeric );
insert into class2(name, sex, age, height, weight) 
select * from class
order by weight, name;

select * from class2;
drop table class2;

/********** 2. Subquery *********************/ 
select name, weight, (
   select count(*) from class as b
   where b.weight < a.weight or (b.weight = a.weight and b.name <= a.name) 
   ) as row_num
from class as a
order by weight , name

/********** 3. Cursor *********************/ 
create table classcursor(name char(8), weight int, row_num int);
declare @name as char(8), @weight as int, @row_num as int

begin tran
   declare rncursor cursor fast_forward for
      select name, weight from class order by weight , Name;
      
   open rncursor;

   -- Set the initial value
   set @row_num = 0;
   fetch next from rncursor into @name, @weight;

   -- Here come a loop 
   while @@fetch_status = 0
   begin 
      set @row_num = @row_num + 1;
      insert into classcursor(name, weight, row_num)
         values(@name, @weight, @row_num)
      fetch next from rncursor into @name, @weight
   end
   close rncursor;
   deallocate rncursor;
commit tran

select * from classcursor
drop table classcursor

/********** 4. Row_number() function *********************/ 
select name, weight, row_number() over(order by weight, name) as rownum
from class