SQL Server: Difference between ROW_NUMBER, RANK, and DENSE_RANK?

In the previous sessions, we have had great discussions on topics such as What is new in .net Core 3.0 and Differences Between Angular 4 and Angular 5. In this article, we are going to know the difference between row_number, rank and dense_rank in SQL. In today's world, there is nothing big that Internet can do without the use of databases and SQL form the oil for those engines. Hence, it becomes important to find out what are row_number, rank and dense_rank in SQL server and how are they important for us.

Actual Picture: What, Why and How?

In order that you understand the concepts better, lets proceed with a real life simple example. Consider there is a table which is created using the following commands.

CREATE TABLE #Tech
(
 TechName Varchar(50)
)

Here, we observe that there is only a single column TechName. Now, that there is a table, we will insert few names for our trick to begin.

INSERT INTO #Tech (TechName) VALUES ('Technology')
INSERT INTO #Tech (TechName) VALUES ('Crowds')
INSERT INTO #Tech (TechName) VALUES ('Anjan')
INSERT INTO #Tech (TechName) VALUES ('Anjan')
INSERT INTO #Tech (TechName) VALUES ('Scott')
INSERT INTO #Tech (TechName) VALUES ('Scott')

Next, what we do is to check if they have been inserted properly and therefore the query stated below will do our work.

SELECT * from #Tech ORDER BY TechName DESC

What is Row_Number

It is a SQL engine built-in function that offers you a facility wherein you could number the rows that are displayed as a result of a query.

Rank

This function will return the rank of each row based on the condition given.

Dense_Rank

This function serves all the rows along with their ranks listed aside them and takes care of the rows that have similar values.

Their Difference

Execute the below query and check the output.

SELECT ROW_NUMBER() OVER (ORDER BY TechName DESC) AS RowNumber,
RANK() OVER (ORDER BY TechName DESC) AS Rank,
DENSE_RANK() OVER (ORDER BY TechName DESC) AS DenseRank
FROM #Tech

You could clearly see what is happening actually!

Conclusion

Now what makes the row_number, rank and dense_rank is that the row_number function returns the list of records along with the row numbers beside them in a sequential manner.

1. Row_Number: There are a total of six records and therefore the row numbers will be all through the 6. 
2. Rank: When it comes to the rank, records that have equal values are tied together. So, two records with values Scott are clubbed together as rank 2 and the next record, Crowds is given rank 4.  So, the number skips 3. 
3. Dense_Rank: The dense_rank function clears this mist by doing a proper job. In this, Crowds record is given rank 3.

Relevant Reading

• Saving Changes not permitted in SQL Server
• Understanding The Relationships in SQL Server
• How to avoid duplicate records in SQL view using Entity Framework
• UNION VS UNION ALL IN SQL SERVER