SQL Server Row Number
Row number is the most common ranking function used in SQL Server. The ROW_NUMBER() function generates a sequential number for each row within a partition in the resultant output. In each partition, the first-row number begins with 1. We must always use the ORDER BY clause to ensure that the numbers are assigned in the proper sequence. The values returned by this function are of BIG INT data type. This function comes with SQL Server 2005 and later MS SQL versions.
The ROW_NUMBER determines the temporary value when the query is executed. If you want to get the numbers in a table, you need to see the IDENTITY property and SEQUENCE. When the ROW_NUMBER function in SQL Server encounters two identical values in the same partition, it assigns different rank numbers to them. The rank number will be determined by the order in which they are displayed.
There is no guarantee that the value returned by this function will be ordered the same for each execution unless the following are true:
The following are the syntax that illustrates the ROW_NUMBER() function:
Let us understand the ROW NUMBER() function's syntax:
It's important to understand this clause that specifies the window or set of rows that the window function operates. The PARTITION BY and ORDER BY are the two possible clauses of the OVER clause. The OVER clause's ORDER BY expression is supported when the rows must come in a specific order for the function to execute.
This clause allows us to sort the rows of the result set within each partition. It is a required clause because the ROW_NUMBER() function is order-dependent.
Let us understand how the ROW_NUMBER function works in the SQL Server table with an example. First, we will create a table named "Persons" using the below statement:
Next, we will add some records into this table using the below statement:
Next, verify the data using the SELECT statement. We will get the below output:
1. Simple ROW_NUMBER() Example
The following statement displays the person's detail and adds a sequential integer number to each row by using the ROW_NUMBER():
Here, we have not specified the PARTITION BY clause so that the ROW_NUMBER() function will treat the whole result set as a single partition. After execution of the statement, we will get the following output:
2. ROW_NUMBER() over Partitions Example
The ROW NUMBER() function is used in this example provides a sequential number to each record within a partition in a table. It always reinitialized the row number when the year changes:
Here, we have used the PARTITION BY clause that divides the 'Persons' table into partitions based on the 'years' column. After execution, we will get the below output:
3. ROW_NUMBER() Example for Pagination
We can also use the ROW_NUMBER() function for pagination. For example, if we want to get all of a person's information in an application by pages, we'll first assign each row a sequential number using the ROW_NUMBER() function. Second, sort the rows by the requested page.
The following statement explains it more clearly:
It will give the following output:
If we do not want to use the subquery, we can use the common table expression (CTE) as below statement:
In this example, we have first specified the CTE that uses the ROW_NUMBER() function to assign each row in the result set sequentially. Second, the outer query returned the desired result. After execution, we will get the following output:
In this article, we learned how to give a sequential number to each record within a partition of a query using the ROW NUMBER() function in SQL Server.