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
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.
-- 设置执行时间开始,用来查看性能的 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 ;
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).
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;
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;
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 ;
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!