ROW_NUMBER Function in SQL Server

ROW_NUMBER() is a common function in SQL Server. It generates a number in sequential order for each row, beginning with 1.

To make sure that the numbers are assigned in the correct order, we must always use the ORDER BY procedure. Data types of BIG INT are used for the values that this function returns.

We will examine in depth how the Row_Number() function works in SQL Server. Let’s get started.

What is SQL Server Row_Number() and How Does it Work

An SQL window function called ROW_NUMBER() assigns a distinct number to each row in a result set. This function is used in pagination and ranking tasks. The numbers are assigned based on an ordered data partition.

A particular class of SQL functions called window functions calculates values across data rows connected to the current row. These rows are called windows or window frames.

Syntax for Row_Number() Function in SQL Server

ROW_NUMBER() OVER (  
    [PARTITION BY partition_expression, ... ]  
    ORDER BY sort_expression [ASC | DESC], ...  
)  

Look into the explanation below for a clear understanding.

OVER—This is an important clause that specifies the set of rows where the window function operates. The two main clauses of the over clause are PARTITION BY and ORDER BY. It will support only when the row comes in sequential order for the function to execute.

PARTITION BY

  • This optional clause divides the result set into partitions (groups of rows). The ROW NUMBER() function is applied to all the partitions, and one number is assigned separately.
  • If the partition by clause is omitted, the ROW_NUMBER function will treat the entire result as a single partition and provide ranking in top-to-bottom order.

ORDER BY – This clause allows us to arrange the rows of the result set within each partition. It is necessary due to the order-dependency of the ROW_NUMBER() function.

Simple Example for Row_Number() Function

Let’s see a simple example of the existing table and how the row_number works. Here, I have the EmpDetails. I have used the syntax below for that.


SELECT *, ROW_NUMBER()   
OVER (ORDER BY salary) AS row_num  
FROM EmpDetails;  
Row number in SQL Server

This is the simple row_num that comes with the sequential number in SQL Server.

Row_Number() over Partitions

The ROW NUMBER() function used in this example provides a sequential number to each record within a partition in a table. For instance, if the record has the same value twice or thrice, then for the next value, it will reinitialize the row number.

I have the table name StudentDetail with the column, values, and output below.

CREATE TABLE StudentDetail (    
  Studentid int NOT NULL,    
  studentname varchar(45),    
  country varchar(25) ,  
  Age int NOT NULL    
);  

INSERT INTO StudentDetail (Studentid, studentname, country, Age)     
VALUES (101, 'Steffen','USA',18),     
(102, 'Joseph', 'India',18),     
(103, 'Kevin', 'USA', 18),     
(104, 'Thompson', 'France',17),   
(106, 'Donald', 'England',17),    
(107, 'Joseph', 'India',16),    
(108, 'Matthew','France',16);

select * from StudentDetail
Row_number in SQL Server

We will apply ROW_NUMBER over the partition using the syntax below for the above table.

SELECT Studentid, studentname, country, Age,   
ROW_NUMBER() OVER ( PARTITION BY Age ORDER BY country) AS row_num  
FROM StudentDetail;  
SQL Server Row_number

We have used the ROW_NUMBER partition here, which will always reinitialize the row number when the age changes.

Row_Number() without Partition BY

SQL Server’s ROW_NUMBER() function is a flexible tool that allows users to assign each row a unique number. It is also effective without PARTITION BY. See the syntax below.

SELECT

ROW_NUMBER() OVER (ORDER BY YourOrderByColumn) AS RowNum,

OtherColumns

FROM

YourTableName;

Look at the below syntax for my table StudentDetail. Studentname will be in descending order.

SELECT * , ROW_NUMBER() OVER (ORDER BY studentname DESC) AS studentid
FROM StudentDetail
Row_number without partiton in SQL Server

This is how we can use Row_function without partition in SQL Server.

Advantages of ROW_NUMBER() With PARTITION BY

  • It makes advanced partition ranking possible.
  • Allows for distinct row numbers within particular groupings.
  • It gives more leeway in situations where a thorough analysis is required.

Advantages of ROW_NUMBER() Without PARTITION BY

  • Clear-cut and easy to understand.
  • Simplifies the process of ranking items based on a specific column.
  • Possibly provide improved performance, particularly for smaller result sets.

Row_Number() Example for Pagination

For pagination, we can also use the ROW_NUMBER() function. For example, we would use the ROW_NUMBER() function to first assign a sequential number to each row to retrieve all of the information about a person in an application by pages. Next, arrange the rows according to the desired page.

Here, I want the particular details of the rows; for a better understanding, look at the syntax and output for the table below.

SELECT * FROM ( SELECT ROW_NUMBER()   
OVER (ORDER BY Age) AS row_num, Studentid, studentname, country, Age
FROM StudentDetail) P  
WHERE row_num > 2 AND row_num <= 5; 
Row_number function in SQL Server

Conclusion

It is beneficial when we need to perform the necessary operations and have the sequencing based on partition.

We must use the ORDER BY clause when using the ROW_NUMBER() function. The primary application of ROW_NUMBER() is ranking rows according to various partitions, which is highly beneficial for data analysis.

You may like to read: