All Three function work as per order by clause
The row_number() function always generates a unique ranking even with duplicate records.
RANK() behaves like ROW_NUMBER(), except that “equal” rows are ranked the same.
Dense_Rank() Function is similar to Rank with the only difference, this will not leave gaps between groups.
SELECT *, ROW_NUMBER() OVER(order by Col) as [ROW_NUMBER] ,RANK() OVER(ORDER BY Col) as [RANK] ,DENSE_RANK() OVER(ORDER BY Col) as [DENSE_RANK] FROM ( VALUES('a'),('a'),('a'),('b'),('c'),('c'),('d'),('e') ) Tab(Col)
Other References