What is the difference among Row_Number, Rank and Dense_Rank

row_number numbers the rows 1, 2, 3, etc by the columns in the ORDER BY clause, and if there are ties, it is arbitrary which rows that gets the same number.

rank and dense_rank are similar to row_number, but when there are ties, they will give the same value to the tied values. rank will keep the ranking, so the numbering may go 1, 2, 2, 4 etc, whereas dense_rank will never give any gaps.

Study this example:

CREATE TABLE #data(MainID     int           NOT NULL,
                   Component  varchar(10)   NOT NULL,
                   Value      decimal(12,2) NOT NULL,
                   PRIMARY KEY (MainID, Component)
)
go
INSERT #data (MainID, Component, Value)
  VALUES(1, 'ALPHA', 12),
        (1, 'BETA', 12),
        (1, 'GAMMA', 13),
        (1, 'DELTA', 12.23),
        (1, 'EPSILON', 11.1),
        (2, 'London', 9.12),
        (2, 'New York', 8.11),
        (2, 'Mumbai',  9.12)
go
SELECT MainID, Component, Value, 
       rowno = row_number() OVER(PARTITION BY MainID ORDER BY Value),
       rank  = rank() OVER(PARTITION BY MainID ORDER BY Value),
       drank = dense_rank() OVER(PARTITION BY MainID ORDER BY Value)
FROM  #data 
ORDER BY MainID, Value
go
DROP TABLE #data

If there are no ties, the three functions will yield the same result.