New sql features

Window functions

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

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

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

    Scheme

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

Console

Execute

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

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

    Scheme

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.