Difference between DENSE_RANK() and ROW_NUMBER()

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