What are the paging methods in SQL server?

This article talks about the paging method of SQL server, using the SQL server 2012 version. In the following, pageIndex is used to represent the number of pages, and pageSize represents the records contained on one page. And the following involves specific examples, set the query page 2, each page contains 10 records.

First of all, let’s talk about the difference between SQL server’s paging and MySQL’s paging. MySQL’s paging can be completed directly by using limit (pageIndex-1) and pageSize. However, SQL server does not have the limit keyword, only something like limit. The top keyword. So paging is more troublesome.

There are only four types of SQL server paging that I know: triple loop; using max (primary key); using the row_number keyword, offset/fetch next keyword (summarized by collecting other people’s methods on the Internet , there should be only these four methods at present, other methods are based on this deformation).

Partial records of the student table to be queried

#Method 1: Triple loop

Idea

  First take the first 20 page, then reverse order, and take the first 10 records in reverse order, so that you can get the data required for paging, but the order is reversed. You can then return it in reverse order, or you can stop sorting and hand it over directly to the front-end for sorting.

There is another method that can be considered to be of this type. I won’t put the code here. I will just talk about the idea, which is to first query the first 10 records, and then use not in to exclude these 10 records, and then Inquire.

Code implementation

-- 设置执行时间开始，用来查看性能的
set statistics time on ;
-- 分页查询（通用型）
select * 
from (select top pageSize * 
from (select top (pageIndex*pageSize) * 
from student 
order by sNo asc ) -- 其中里面这层，必须指定按照升序排序，省略的话，查询出的结果是错误的。
as temp_sum_student 
order by sNo desc ) temp_order
order by sNo asc

-- 分页查询第2页，每页有10条记录
select * 
from (select top 10 * 
from (select top 20 * 
from student 
order by sNo asc ) -- 其中里面这层，必须指定按照升序排序，省略的话，查询出的结果是错误的。
as temp_sum_student 
order by sNo desc ) temp_order
order by sNo asc
;

Query results and time

Method 2: Use max (Primary key)

 First, top the first 11 row records, then use max (id) to get the largest id, and then re-query the first 10 records in this table, but you must add conditions, where id>max( id).

Code implementation

set statistics time on;
-- 分页查询（通用型）
select top pageSize * 
from student 
where sNo>=
(select max(sNo) 
from (select top ((pageIndex-1)*pageSize+1) sNo
from student 
order by  sNo asc) temp_max_ids) 
order by sNo;


-- 分页查询第2页，每页有10条记录
select top 10 * 
from student 
where sNo>=
(select max(sNo) 
from (select top 11 sNo
from student 
order by  sNo asc) temp_max_ids) 
order by sNo;

Query results and time

##Method 3: Use row_number Keyword

  Directly use the row_number() over(order by id) function to calculate the number of rows, select the corresponding row number and return it, but this keyword is only available in SQL server 2005 or above.

SQL implementation

set statistics time on;
-- 分页查询（通用型）
select top pageSize * 
from (select row_number() 
over(order by sno asc) as rownumber,* 
from student) temp_row
where rownumber>((pageIndex-1)*pageSize);

set statistics time on;
-- 分页查询第2页，每页有10条记录
select top 10 * 
from (select row_number() 
over(order by sno asc) as rownumber,* 
from student) temp_row
where rownumber>10;

Query results and time

The fourth method: offset /fetch next (only available in 2012 version and above)

Code implementation

set statistics time on;
-- 分页查询（通用型）
select * from student
order by sno 
offset ((@pageIndex-1)*@pageSize) rows
fetch next @pageSize rows only;

-- 分页查询第2页，每页有10条记录
select * from student
order by sno  
offset 10 rows
fetch next 10 rows only ;

offset A rows, discard the first A record, fetch next B rows only, read backward B data.

Results and running time

Encapsulated stored procedure

Finally, I encapsulated a The paging stored procedure is convenient for everyone to call, so that when the time comes to write paging, you can directly call this stored procedure.

Paging stored procedure

create procedure paging_procedure
(	@pageIndex int, -- 第几页
	@pageSize int  -- 每页包含的记录数
)
as
begin 
	select top (select @pageSize) *     -- 这里注意一下，不能直接把变量放在这里，要用select
	from (select row_number() over(order by sno) as rownumber,* 
			from student) temp_row 
	where rownumber>(@pageIndex-1)*@pageSize;
end

-- 到时候直接调用就可以了，执行如下的语句进行调用分页的存储过程
exec paging_procedure @pageIndex=2,@pageSize=10;

Summary

　According to the execution time of the above four paging methods, we can know that among the above four paging methods, the second and third The performance of the third and fourth methods is similar, but the performance of the first method is very poor and is not recommended. Also, this blog is testing a small amount of data and has not paged a large amount of data, so it is not clear which method has better performance when a large amount of data needs to be paged. I recommend the fourth method here. After all, the fourth method is a new method introduced after the SQL server company upgraded, so it should theoretically have better performance and readability.

Related recommendations: "

mysql tutorial"

The above is the detailed content of What are the paging methods in SQL server?. For more information, please follow other related articles on the PHP Chinese website!