The difference between DENSE_RANK() and Row_number() is that Row_Number will generate a sequence of record one by one while DENSE_RANK() will also generate a sequence, but it is depend upon the field if field value is repeated then Rank of Dense will also repeat.
Following is the example that demonstrate a difference between both of them.
create table department(
dept_name char(20) not null,
emp_cnt int not null,
budget float,
date_month datetime);
GO
insert into department values('Research', 5, 50000, '01.01.2002');
insert into department values('Research', 10, 70000, '01.02.2002');
insert into department values('Research', 5, 65000, '01.07.2002');
insert into department values('Accounting', 5, 10000, '01.07.2002');
insert into department values('Accounting', 10, 40000, '01.02.2002');
insert into department values('Accounting', 6, 30000, '01.01.2002');
insert into department values('Accounting', 6, 40000, '01.02.2003');
insert into department values('Marketing', 6, 10000, '01.01.2003');
insert into department values('Marketing', 10, 40000, '01.02.2003');
insert into department values('Marketing', 3, 30000, '01.07.2003');
insert into department values('Marketing', 5, 40000, '01.01.2003');
GO
SELECT DENSE_RANK() OVER(ORDER BY budget DESC) AS rank_budget,
ROW_NUMBER() OVER(ORDER BY budget DESC) AS row_number,dept_name, emp_cnt, budget
FROM department
WHERE budget <= 50000;
GO
rank_budget row_number dept_name emp_cnt budget
---------------- -------------------- -------------------- ---------- ------------
1 1 Research 5 50000
2 2 Accounting 10 40000
2 3 Accounting 6 40000
2 4 Marketing 10 40000
2 5 Marketing 5 40000
3 6 Marketing 3 30000 3 7 Accounting 6 30000
4 8 Accounting 5 10000
4 9 Marketing 6 10000
