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