ROW_NUMBER function in SQL server ?

To return the Sequential number of row within the partition of a result set we can use the Row_Number function.

 For each partition the starting row number will be 1.

Its return type is BIGINT.

SYNTAX

ROW_NUMBER() OVER ( [PARTITION BY VALUE_EXPRESSION] ORDER BY CLAUSE)

EXAMPLE

In below example I have created Rownumber based on the UID column.

In below example I have created the department-wise Rownumber using Partition by clause.

Row_Number order will not exactly generated unless it satisfies the following condition.

• Values of ORDER BY columns are Unique
• Values of Partitioned column are Unique
• Values of both ORDER BY and Partitioned columns are Unique.