Detailed query for SQL Server and Oracle paging queries _mssql

Whether it is the implementation of the paging query code in the DRP or interview questions to see the investigation of the paging query, give me a hint: Paging query is important. It must be considered when the volume of data is large. Never took the time to stop to get a good summary here. Now look at the content of the page query in Oracle video again and find it easy to understand.

1. Page Splitting algorithm
When I first looked up information on the Internet, I saw a lot of paging content and I felt a lot of confusion. Not really. The information on the Internet is very similar. The problem is with me. I don't get it. What are the prerequisites for paging? We all know that as long as there are pagination, these variables are involved: how many records per page (pageSize), current page (Pagenow), Total Records (totalrecords), total number of pages (totalpages), start Page (Beginrow), End page (endrow). The data paging algorithm on the web is useful to pagesize, useful to Beginpage and endpage. In fact, these variables need to be categorized: I divide them into three categories :
A. Needs to be queried from the database: Totalrecords. "SELECT COUNT (*) from TableName"
B. The most basic needs of user-provided: PageSize and Pagenow. (Personally think this is the premise of pagination algorithm)
C. Derived from other variables: TotalPages, Beginrow and Endrow. (it needs to be calculated that Beginrow and endrow are required for paging queries, TotalPages is the information that the page needs to provide). Specific formula for calculation:

Totalpages:if ((totalrecords% pageSize) = = 0) {  
             totalpages = totalrecords/pagesize;  
           } else {  
             totalpages = to Talrecords/pagesize + 1;  
           } 
Beginrow: (pageNow-1) * PageSize +1 
endrow:   

So that the values of these variables can be obtained. Please take a look at the 2 and 3 parts specifically how to use it.

a common paging method in 2.Oracle
In fact, whether Oracle or SQL Server, the basis for implementing a paging query is subqueries. In my own words: Select the set of the Select.
Oracle paging is available in three different ways. I only speak one easy to understand here. Take the Employee table (EMP) for example. Suppose there are 10 records, now paging requires 5 records per page, and the current page is 2. The query comes out with a record of 6-10. Let's start with a specific number and then replace it with a variable.
Oracle realizes the first step:Select A.*,rownum rn from (SELECT * from EMP) A; where RowNum is an Oracle internal allocation line number. The SELECT * from emp in parentheses queries all the records in the EMP table. Then we further query the results of the query as a view. The select outside adds a rownum to the whole of the EMP, so that subsequent queries can be used.
Oracle implements step two:Select A.*,rownum rn from (SELECT * from EMP) a where rownum<=10; the second step is to query for records with a travel number less than or equal to 10. There may be such a question as to why not write rownum>=6 and rownum<=10 directly. Don't solve the problem. The Oracle internal mechanism does not support this type of notation.
Oracle realizes step three:SELECT * FROM (select A.*,rownum rn a where rownum<=10) where rn>=6 OK, so you can complete the query 6-10 records.
At last. We convert to variables. It may be in a Java program or in a pl/sql.
Three conversions are required: "EMP "position for the specific table name," 6 "position for (pageNow-1) * PageSize + 1," 10 "position for Pagenow * PageSize.
This method can be used as a template, easy to modify. All changes only need to change the innermost layer is OK. For example, to query for a specified column: Modify the innermost select Ename,sal from emp; Sort by salary column: Select Ename,sal from EMP ordered by Sal; all you need to do is modify the innermost layer.

A common paging method in 3.SQLServer
Let's use the example of the employee table to talk about the implementation of paging in SQL Server
use of the first top:
SQL Server implements the first step: Select Top * from EMP Empid, and in ascending order of employee ID, remove the first 10 records.
SQL Server implements the second step: select Top 5* from (select Top * from EMP-empid) A ORDER by empid Desc. Arrange the 10 records that are taken out in descending order of the employee number and then remove the 5 records. The first time here is sorted in ascending order, and the second in descending order is tricky. I didn't expect top to have the effect. The 10 position here is replaced by the variable Pagenow * pagesize and 5 by pagesize.
use of the second top and in:
Select Top 5 * "from EMP where Empid" (select top empid from emp order by Empid) Order by empid Desc; The 10 position here is replaced by the variable Pagenow * pagesize and 5 by pagesize.
Other queries are similar, and there is no more to repeat.

The above is the two kinds of database to achieve the paging function case, I hope to help you learn.