Difference between ROW_NUMBER, RANK, DENSE_RANK, NTILE in SQL Server

ROW_NUMBER, RANK, DENSE_RANK, NTILE are functions in SQL server and returns numeric output by different sequence order. These functions are having some similarities and significant difference.

Similarity:

1. Should have OVER clause and ORDER BY clause inside the OVER clause.
2. Can have PARTITION BY clause inside the OVER clause.

ROW_NUMBER: Returns the sequence and unique number for each group based on the fields applied in PARTITION BY clause. If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.

RANK: Similar to ROW_NUMBER function and Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

DENSE_RANK: Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

NTILE: Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

Let’s see a simple example:

SELECT 
ROW_NUMBER() OVER(ORDER by Employee_City ASC) AS ROWNUM_CITYWISE,
ROW_NUMBER() OVER(PARTITION BY Employee_City ORDER by Employee_City ASC) AS ROWNUM_PART_CITYWISE,
RANK() OVER(ORDER BY Employee_City ASC) AS RANK_CITYWISE,
DENSE_RANK() OVER(ORDER BY Employee_City ASC) AS DENSRANK_CITYWISE,
NTILE(4) OVER(ORDER BY EMPLOYEE_CITY ASC) AS NTILE_CITYWISE,
Employee_Id,
Employee_Name,
Employee_Address,
Employee_City,
Employee_State,
Employee_Salary
FROM Employee

Below output explains about four different functions aspects.

As shown in above result, ROW_NUMBER returns a sequence and unique value for each group if PARTITION clause applied on Employee_Address field. Otherwise the ROW_NUMBER returns unique and sequence value for whole records.
RANK function returns same value for each group but it would not continue the sequence number for next group.
DENSE_RANK funtion is similar to RANK funtion, but it is continue the sequence value for next group.
NTILE function is splitting four groups from the result set which we applied in NTILE function.

Also see below articles

Monitoring and Performance Tuning Tips for SQL Server database
Implement Best Practice and Performance tuning tips in SSIS and ETL Development
SQL Server/Developer Interview Questions and Answers for Experienced Professional