Pagination using ROW_NUMBER with a CTE In SQL Server

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.

1.

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;

2.

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)

Summary

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.