Paging records

This problem often arises in cases where the number of rows returned by the query exceeds a reasonable size. An example is the presentation of search results or messages on the forums site. Results are sorted by certain criteria (e.g., by relevance or by message date) and then divided by N lines per page. The main problem here is not to load the client the entire set of rows, and only load the requested user page (very few people through all the pages in a row). In the absence of such a possibility would have to perform paging by software client, which negatively affects the traffic and page load time.

So, we need to output, along with detailed data, the total number of rows (or number of pages) and page number for each record returned by the query. If we are able to do it, not to return the entire result set on the client, we can on the basis of this query to create a stored procedure, which as the input parameters will be passed the required number of records per page and the page number, and the output a recordset from the specified page. Such an approach would be sparingly traffic and navigating through the pages will use the cached execution plan of a stored procedure.

For example, consider the breakdown of 2 entries per page rows from Laptop, ordered by descending price.

That way you can add a column containing the total number of rows in the table below:

SELECT *, COUNT(*) OVER() AS total
FROM Laptop

Note that this could be done within the SQL-92 standard using a subquery:

SELECT *, (SELECT COUNT(*) FROM Laptop) AS total   
FROM Laptop

However, imagine that we do not use a simple table (Laptop), and the cumbersome query, which may contain dozens or hundreds of rows. In “window” query does not have undergone changes, and in “classical” case would have to completely duplicate query code in a subquery to calculate the number of rows.

To calculate the number of pages, we can use following simple algorithm:

• If the number of query rows divisible by the number of records per page, the result of integer division one to the other gives the number of pages;

• if there is a nonzero remainder of integer division of the query rows on the number of records on the page, the result of integer division adds one (the last page is incomplete).

This algorithm is implemented by standard means using the CASE operator:

SELECT *,   
      CASE WHEN total % 2 = 0 THEN total/2 ELSE total/2 + 1 END AS num_of_pages  
FROM (  
      SELECT *, COUNT(*) OVER() AS total   
      FROM Laptop  
      ) X;

To get to each row of query the page number on which it must fall, we can apply the same algorithm, but apply it is necessary not to the total number of rows (total), and row number. This row number we can get through a ranking ROW_NUMBER function, performing the required sorting by price:

SELECT *,   
      CASE WHEN num % 2 = 0 THEN num/2 ELSE num/2 + 1 END AS page_num,   
      CASE WHEN total % 2 = 0 THEN total/2 ELSE total/2 + 1 END AS num_of_pages  
FROM (  
      SELECT *, ROW_NUMBER() OVER(ORDER BY price DESC) AS num,   
             COUNT(*) OVER() AS total   
      FROM Laptop  
) X;

Stored procedure, as described above, might look like:

CREATE PROCEDURE paging   
@n int -- number of records per page  
, @p int =1 -- the page number by default - the first  
AS  
SELECT * FROM  
 (SELECT *,   
   CASE WHEN num % @n = 0 THEN num/@n ELSE num/@n + 1 END AS page_num,   
   CASE WHEN total % @n = 0 THEN total/@n ELSE total/@n + 1 END AS num_of_pages  
  FROM   
  (SELECT *,   
         ROW_NUMBER() OVER(ORDER BY price DESC) AS num,   
         COUNT(*) OVER() AS total FROM Laptop  
  ) X  
 ) Y  
WHERE page_num = @p;  
GO

Thus, if we need to get a second page with 2 entries on the page, just write

exec paging @n=2, @p=2

As a result, we obtain:

But so will look incomplete second page, if the number of records per page will be equal to four:

exec paging @n=4, @p=2

New features of Standard, which have been included into SQL Server 2012, make paging a very simple procedure. We are talking about new optional keywords in ORDER BY clause, namely OFFSET and FETCH. These new keywords allow you to specify how many rows from the query result have to be returned to client and from which row they should be begun.

Now this expanded syntax of ORDER BY clause takes the form:

ORDER BY {< expression >
    [ ASC | DESC ]}
    [ ,...n ]
[
OFFSET < integer_expression_1 > { ROW | ROWS }
[FETCH { FIRST | NEXT } < integer_expression_2 > { ROW | ROWS } ONLY]
]

FIRST and NEXT are synonyms as well as ROW and ROWS, i.e. you can use any of two variants.

integer_expression_2 presents a number of rows which have to be returned, whereas integer_expression_1 is a number of rows from the beginning of sorted result set, which need to be missed before return. If FETCH keyword is absent, all rows will be returned beginning from integer_expression_1 + 1.

With aid of these new features, paging procedure which has been considered above can be rewritten in a very simple manner:

create proc paging
  @n int =2 -- number of rows per page, 2 by default
, @p int =1 -- number of page to return, first by default
AS
SELECT * FROM Laptop
ORDER BY price DESC offset @n*(@p-1) rows fetch next @n rows only;

Note that standard syntax of ORDER BY clause is supported by PostgreSQL also.

Suggested exercises: 107