Directly on the code
1 --top not in mode2 SelectTop Bar Number * fromTableName3 whereId notinch(SelectTop Bar Number * Page Id fromtablename)4 5 6 7--row_number () over () mode8 Select* from ( 9 Select*, Row_number () over (Order by Id) as RowNumber fromTableNameTen) asb One whereRowNumber between current page-1* Number of and pages *article number A - - the --offset Fetch Next Method--SQL2012 more than version support - Select* fromTableName -Order by Id offset page row fetch next number row only
Before we discuss performance, we need to make it clear that when we write SQL statements, we try to reduce the output of unnecessary fields, in the case of demo, so we use the *, not in practice!! There is also the need for business logic, such as query conditions, to establish a suitable aggregate index and non-aggregated index, the index for the efficiency of the search is very large, the index in SQL is actually to establish some kind of balance search tree
- The use of the Top–not in-top scheme is complex, containing in statements, inefficient, but compatible with a version of SQL Server.
- Using the Row_number method to achieve the ease of paging is moderate, high efficiency. Skip and take in LINQ are also paged in this way, and should be the more extensive paging method currently in use.
- The Ffset FETCH method was introduced in SQL Server CE 4.0 because this article does not have SQL Server 2012 and the test data. From the Comparing-performance-for-different-sql-server-paging-methods article and the SQL Server 2012 paging method analysis in the garden (offset and fetch), The performance should be better.
The above is a summary of the SQL paging, I hope that you have some help.
Three ways to page out SQL Server