SQL Server Paging

1, why do you want to split pages? When the data is displayed, we will not show all the data at once, for example, there are 10,000 data in the table, should we show the 10,000 data in one time?! Even if displayed to the user, the user can not see. Therefore, whether from the point of view of efficiency or from the perspective of the user, only the query part to show the user can be. This speed is fast and the user looks convenient. This also requires a small number of queries from a large table to be displayed to the user, so this small portion of the data is implemented by paging. 2, the essence of paging: from a big data query a small part out. 3, the premise of paging: to page query, or page display before the first to determine what sort, and then to determine which records should be on the first page, which records should be on the second page. 4, pagination method: Top pagination Method and Row_number () method. 5, Top Page method: 5.1, the idea: first to sort, and then to check which page, then the ID of which page before the query out, and then from the total data to exclude these IDs, the rest is you have not seen, and then did not see the row of the order, the last to take the first n can be. 5.2. Specific implementation: Select TOP (pageSize) * FROM table name where column name not in (select Top (pagesize* (pageIndex-1)) Column name from table name order by   column name as c) Order By column name, such as: Select Top (pageSize) * from Student where autoid not in (select Top (pagesize* (pageIndex-1)) Autoid from S Tudent ORDER BY autoid ASC) The order by autoid; 6, using row_number () to achieve paging: 6.1, top paging is a long-ago paging method, the efficiency is not very high. SQLServer2005 appears after the Row_number () paging method. 6.2, paging ideas: First renumber the existing data, and then according to the user to view the number of records per page, as well as to see the first few pages. Make sure to query the first few to the first few. 6.3, Concrete implementation: first sort, then number. Select *,rn=row_number () over (order by CustomerId ASC) from Customers; the last implementation: 7 per page, to see page 8th. Select * from (select *,rn=row_number () over(Order by CustomerId ASC) from Customers) as Twhere t.rn between (8-1) *7+1 and 8*7; select * from (select Rn=row _number () over (order by autoid ASC), * from Student) as Swhere s.rn between (3-1) *7+1 and 3*7;select     Tsnam e,tsage,tclassnamefrom    tblstudent as TS INNER join Tblclass as TC on TS.TSCLASSID=TC.TCLASSID;  SE lect     tsname,tsage,tclassnamefrom    tblstudent as TS INNER join Tblclass as TC on Ts.tsclassi D=tc.tclassidwhere ts.tsage>20; select     T1.tsname,t1.tsage,t2.tclassname,t3.tscore  from    tblstudent as T1 inner join Tblclass as T2 on T1.tsclassid=t2.tclassidinner joins Tblscore as T3 on t1.tsid=t3.tsid;    Summary: When querying the first page, using the top method, the most efficient, in addition to the first page of the query with Row_number () implementation of paging.

SQL Server Paging