In SQL SERVER, ROW_NUMBER returns row numbers for rows in a SELECT query without any skip in the serial order. It doesn’t depend on any custom criteria and is simply a serial number for each row returned by the query.
On the other hand, RANK returns the RANK for a single row in an executing query. It skips N-1 rows if there are duplicate rows for the data returned by a partition condition (based on which the records are grouped).
For example, the below query returns serialized numbers per department in the table. It skips numbering if there are duplicate rows in the selected rows.
SELECT RANK() OVER (PARTITION BY DEPT ORDER BY SAL) AS RANK FROM EMP;DENSE_RANK is similar to RANK, but returns the RANK for the rows based on an executing query. DENSE_RANK doesn’t skip any ranks if there are duplicates in the grouping.
SELECT DENSE_RANK() OVER (PARTITION BY DEPT ORDER BY SAL) AS DRANK FROM EMP;To summarize,
- ROW_NUMBER returns row numbers for rows in a SELECT query without any skip in the serial order
- RANK returns the RANK for a single row in an executing query and skips N-1 rows if there are duplicate rows
- DENSE_RANK returns the RANK for the rows based on an executing query but doesn’t skip any ranks if there are duplicates in the grouping.
