What are the differences between the ROW_NUMBER(), RANK(), and DENSE_RANK() functions in SQL Server?

ROW_NUMBER(), RANK(), and DENSE_RANK() are three different functions in SQL Server that are used to generate a sequence of unique values based on a given column’s order.

## ROW_NUMBER()

ROW_NUMBER() function generates consecutive integers for each row of the result set. It assigns a unique value to each row within the partition specified in the OVER() clause. Partitioning divides the result set by one or more columns or expressions specified in the PARTITION BY clause. If PARTITION BY is not specified, the whole result set is treated as a single partition.

The syntax for the ROW_NUMBER() function is:

ROW_NUMBER() OVER ( PARTITION BY <column_name> ORDER BY <column_name> )

Here is an example of the ROW_NUMBER() function:

SELECT ROW_NUMBER() OVER(ORDER BY sales) AS RowNumber, * FROM sales_table

This query will return the sales table with a new column called RowNumber which will be the row number of each row based on the sales column in ascending order.

## RANK()

RANK() function assigns a value to each row within the partition based on the ordinal position of the row within the result set. The rank of the first row is 1, the rank of the second row is 2, and so on. If there are ties, then multiple rows will receive the same rank, and the subsequent rank will be skipped.

The syntax for the RANK() function is:

RANK() OVER ( PARTITION BY <column_name> ORDER BY <column_name> )

Here is an example of the RANK() function:

SELECT RANK() OVER(PARTITION BY country ORDER BY sales DESC) AS Rank, * FROM sales_table

This query will return the sales table with a new column called Rank, which will assign a rank to each country based on their sales in descending order.

## DENSE_RANK()

DENSE_RANK() function is similar to the RANK() function, but if there are ties, the same rank is not skipped. This function assigns a unique rank to each distinct value within the partition based on their order in the result.

The syntax for the DENSE_RANK() function is:

DENSE_RANK() OVER ( PARTITION BY <column_name> ORDER BY <column_name> )

Here is an example of the DENSE_RANK() function:

SELECT DENSE_RANK() OVER(ORDER BY sales) AS Rank, * FROM sales_table

This query will return the sales table with a new column called Rank, which will assign a dense rank to each row based on the sales column in ascending order.

In conclusion, the ROW_NUMBER() function assigns unique values to distinct rows in a partition, regardless of the presence of ties, while the RANK() and DENSE_RANK() functions assign unique values to each distinct value in a partition, with RANK() skipping ranks in the presence of ties and DENSE_RANK() not skipping ranks.