Sometimes we need database side paging on largest data. Client side paging techniques are insufficient in this case. For this we use ROW_NUMBER() function.
ROW_NUMBER() method is use with the number of a row in a given recordset. ROW_NUMBER function selects scalar value for all rows that is displayed by ordered column.
ROW_NUMBER() method helps in limiting the number of rows returned from a query, also it assigns a unique number to each row to which it is applied.
Syntax: ROW_NUMBER() OVER ()
We can perform logical paging to display results based on the requirement on selected values to be displayed. We must use this technique over ROW_NUMBER valued column with BETWEEN statement. Sub query for using filtering row_number valued column is use.
We can select this value with any query like this: Let us suppose we have a existing table.
Query:
- Select * from person.person
Output:
Use of ROW_NUMBER() method:
Query: Select row_number() over (order by BusinessEntityId ) as RowNumber , * from person.person
Output: From our table, the rows are now sorted from 1 to 19972 in ascending order based on column name "BusinessEntityId ".
Now we will learn how to use ROW_NUMBER() function and doing filter work (logical paging) with the us of BETWEEN clause.
Query:
- SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY BusinessEntityId) AS RowNumber, * FROM person.person ) as z WHERE RowNumber BETWEEN 910 AND 13400
Output: We can view the selected rows between 910 to 13400.
