SQL Server ROW_NUMBER Function

Summary: in this tutorial, you will learn how to use the SQL Server ROW_NUMBER() function to assign a sequential integer to each row of a result set.

Introduction to SQL Server ROW_NUMBER() function #

The ROW_NUMBER() is a window function that assigns a sequential integer to each row within the partition of a result set. The row number starts with 1 for the first row in each partition.

The following shows the syntax of the ROW_NUMBER() function:

ROW_NUMBER() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)Code language: SQL (Structured Query Language) (sql)

Let’s examine the syntax of the ROW_NUMBER() function in detail.

PARTITION BY #

The PARTITION BY clause divides the result set into partitions (another term for groups of rows). The ROW_NUMBER() function is applied to each partition separately and reinitialized the row number for each partition.

The PARTITION BY clause is optional. If you skip it, the ROW_NUMBER() function will treat the whole result set as a single partition.

ORDER BY #

The ORDER BY clause defines the logical order of the rows within each partition of the result set. The ORDER BY clause is mandatory because the ROW_NUMBER() function is order-sensitive.

SQL Server ROW_NUMBER() function examples #

We’ll use the sales.customers table from the sample database to demonstrate the ROW_NUMBER() function.

Using SQL Server ROW_NUMBER() function over a result set example #

The following statement uses the ROW_NUMBER() to assign each customer row a sequential number:

SELECT 
   ROW_NUMBER() OVER (
	ORDER BY first_name
   ) row_num,
   first_name, 
   last_name, 
   city
FROM 
   sales.customers;Code language: SQL (Structured Query Language) (sql)

Here is the partial output: