ROW_NUMBER with a CTE (Common Table Expression) is used for Pagination in query results. This was a go-to method with SQL Server 2008. Later, SQL Server 2012 introduced the OFFSET-FETCH option to simplify pagination using queries with improved performance. Pagination is a way to show results into a small number of rows and pages. Instead of showing the entire result set on one page, we can divide rows into different pages.
We often see Google search results showing only 1-10 results on the 1st page. There Onwards 11-20 records on the 2nd page and so on. Same with the mailboxes we see first 50 recent emails and we can see the next 50 records on the second page. Classic examples of Pagination.
In this tech-recipes post, we will learn how to do Pagination using ROW_NUMBER with a CTE. Furthermore, we will evaluate the performance using ROW_NUMBER and OFFSET-FETCH for pagination. You can refer to SQL Server 2012’s OFFSET-FETCH method to do pagination.
Pagination using ROW_NUMBER with a CTE
ROW_NUMBER function assigns incrementing number to each row in result set. We can use this functionality with comparison operators like BETWEEN to implement pagination. Using CTE’s results to filter out needless rows.
Let’s divide the queries into ROW_NUMBER then using CTE to do pagination part.
Following query uses ROW_NUMBER function to assign incrementing numbers to result set. We’re using sys.all_columns – a default view available in SQL Server. The number of rows may vary on a different machine.
USE master; GO --Assigns number from 1 to N - where N is total number of rows. SELECT row_number() over(order by object_id asc) as RowNumber, object_id, name FROM sys.all_columns; --Total rows (10044 - may vary)
ROW_NUMBER In WHERE Clause
Let’s try to use ROW_NUMBER function in the WHERE clause to filter out rows between 11 to 20. We are not allowed to do that because ROW_NUMBER can’t be referenced in the WHERE clause. ROW_NUMBER in WHERE clause is evaluated before SELECT and logically windowed function can appear in SELECT or ORDER BY clause only.
Running the following query results in error.
USE master; GO SELECT row_number() over(order by object_id asc) as RowNumber, object_id, name FROM sys.all_columns WHERE row_number() over(order by object_id asc) BETWEEN 11 AND 20;
Error – Can’t use ROW_NUMBER in WHERE Clause
Msg 4108, Level 15, State 1, Line 9
Windowed functions can only appear in the SELECT or ORDER BY clauses.
Pagination using ROW_NUMBER with CTE
Let’s move the existing query used in the above example to a CTE and take out the filter criteria. Following query works successfully showing the rows between 11 and 20.
WITH Pagination_CTE AS ( SELECT row_number() over(order by object_id asc) as RowNumber, object_id, name FROM sys.all_columns ) SELECT * FROM Pagination_CTE WHERE RowNumber BETWEEN 11 AND 20;
Above query can be written using the OFFSET-FETCH method as following producing a similar result set.
SELECT object_id, name FROM sys.all_columns ORDER BY object_id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Using Variables – Pagination using ROW_NUMBER with CTE
In the following example, we’re using variables to store No. of records per page and a page number. Ideally, on page 1 we should see 1 to 10 records, on page 2 – 11 to 20 records, page 3 – 21 to 30 records and so on.
DECLARE @NoOfRecordsPerPage as INT = 10; DECLARE @PageNumber as INT = 4; WITH Pagination_CTE AS ( SELECT row_number() over(order by object_id asc) as RowNumber, object_id, name FROM sys.all_columns ) SELECT * FROM Pagination_CTE WHERE RowNumber BETWEEN ((@PageNumber - 1) * (@NoOfRecordsPerPage)) + 1 AND (@PageNumber * @NoOfRecordsPerPage);
RowNumber object_id name -------------------- ----------- -------------------------------------- 31 -1067705889 read_io_stall_queued_ms 32 -1067705889 read_io_stall_total_ms 33 -1067705889 read_io_throttled_total 34 -1067705889 reserved_io_limited_by_volume_total 35 -1067705889 volume_name 36 -1067705889 write_bytes_total 37 -1067705889 write_io_completed_total 38 -1067705889 write_io_issued_total 39 -1067705889 write_io_queued_total 40 -1067705889 write_io_stall_queued_ms (10 rows affected)
In this tech-recipes we have learned how to use ROW_NUMBER function with CTE to implement pagination. Similarly we can use OFFSET-FETCH to implement pagination in SQL Server 2012 onwards.