SQL efficient paging (Millions of data records) and SQL Paging

SQL efficient paging (Millions of data records) and SQL Paging

Reference: SQL paging statements

Method 1: the most efficient

Select top page size * FROM (SELECT ROW_NUMBER () OVER (order by id) AS RowNumber, * FROM table1) as a where RowNumber> page size * (page number-1) -- Annotation: first, use Row_number () to add a row number for each row in table 1, and name the row number column 'rownumber' in over () in this method, 'rownumber' is sorted in ascending order. Then, the 'rownumber' column and all the columns in Table 1 form A table A, with the focus on the where condition. If the current page (currentPage) is 2nd, each page displays 10 data records (pageSzie ). The data on the first page is 11-20. Therefore, to display the data on the second page, that is, to display the data on the second page, make RowNumber greater than 10 * (2-1: page size * (current page-1)

Write the preceding method as a stored procedure (table name Location)

If (exists (select * from sys. procedures where name = 'P _ location_paging ') -- if the Stored Procedure p_location_paging has the drop proc p_location_paging -- delete the Stored Procedure gocreate proc p_location_paging (@ pageSize int, @ currentPage int) -- create a stored procedure and define two variables, 'number of entries per page' and 'asselect top (@ pageSize) * from (select ROW_NUMBER () over (order by locid) on the current page) as rowid, * from location) as Awhere rowid> (@ pageSize) * (@ currentPage)-1)

Method 2: Second, efficiency

Select top page size * -- if 10 data entries are displayed on each page, 10 data entries are queried FROM table1WHERE id> -- if the current page is the third page, you need to query 21-30 data records, that is, id> 20 (select isnull (MAX (id), 0) -- query the largest id FROM (select top page size * (current page-1) id FROM table1 order by id -- because the current page is the third page, ten data entries are displayed on each page. So I will: the page size * (current page-1) is to get 20 data records in front of "Current page. Therefore, if max (id) is used to query the largest id and the 20 value is obtained, the id> 20 of the preceding where condition gets the data on the third page, that is, 21-30 data records) as A) order by id

Write the preceding method as a stored procedure: Table Name Location

if(exists(select * from sys.procedures where name='p_location_paging'))drop proc p_location_paginggocreate proc p_location_paging(@pageSize int ,@currentPage int)asselect  top (@pageSize) * from locationwhere locId>(select ISNULL(MAX(locId),0)from (select top ((@pageSize)*(@currentPage-1))locid from location  order by locId) as a)order by locId

Method 3: Worst Effect

Select top page size * FROM table1WHERE id not in -- where Condition Statement limits that the data to be queried is NOT the data contained IN the subquery. Query the 10 data records after "subquery. That is, the data on the current page (-- if the current page is the second page and 10 data entries are displayed on each page, all data in front of the current page is obtained here. Select top page size * (current page-1) id FROM table1 order by id) order by id