This two SQLServer functions are used to number datatable rows in order to obtain a page of these rows. Both functions are very simmilar but they have a key difference that obligues us to use one or the other.

Row_number returns the sequential number of a row within a partition of a result set, starting at 1 for the first row.

On the other hand Dense_rank returns the number of different rows in a table. Or in other words, 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.

Dense_rank function must be used when there's a JOIN and a GROUP BY / DISTINCT in a query. A JOIN can cause the apperance of duplicate rows in a datatable, and then when a GROUP BY / DISTINCT is performed over that datable if Row_number is used the duplicates will not be removed because all rows are different (have a different row_number).

Dense_rank has it's own drawbacks. It's more resource intensive than row_number and when a certain number of rows is reached (> 500.000) it stops working properly (at least in SQLServer 2008 R2). So with this in mind we will need to adapt to different scenarios: absence/presence of JOINs and number of rows, in order to choose one technique or other one.