loading..
Русский    English
08:26

Paging records page 1

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:

Console
Execute
  1. SELECT *, COUNT(*) OVER() AS total
  2. FROM Laptop

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

Console
Execute
  1. SELECT *, (SELECT COUNT(*) FROM Laptop) AS total
  2. 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.

Bookmark and Share
Pages 1 2 3 4
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100