search
HomeDatabaseSQLWhat 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

What are the paging methods in SQL server?

#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

What are the paging methods in SQL server?

What are the paging methods in SQL server?

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

What are the paging methods in SQL server?

What are the paging methods in SQL server?

##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

What are the paging methods in SQL server?

What are the paging methods in SQL server?

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

What are the paging methods in SQL server?

What are the paging methods in SQL server?

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!

Statement
This article is reproduced at:CSDN. If there is any infringement, please contact admin@php.cn delete
SQL: The Language of Databases ExplainedSQL: The Language of Databases ExplainedApr 27, 2025 am 12:14 AM

SQL is the core tool for database operations, used to query, operate and manage databases. 1) SQL allows CRUD operations to be performed, including data query, operations, definition and control. 2) The working principle of SQL includes three steps: parsing, optimizing and executing. 3) Basic usages include creating tables, inserting, querying, updating and deleting data. 4) Advanced usage covers JOIN, subquery and window functions. 5) Common errors include syntax, logic and performance issues, which can be debugged through database error information, check query logic and use the EXPLAIN command. 6) Performance optimization tips include creating indexes, avoiding SELECT* and using JOIN.

SQL: How to Overcome the Learning HurdlesSQL: How to Overcome the Learning HurdlesApr 26, 2025 am 12:25 AM

To become an SQL expert, you should master the following strategies: 1. Understand the basic concepts of databases, such as tables, rows, columns, and indexes. 2. Learn the core concepts and working principles of SQL, including parsing, optimization and execution processes. 3. Proficient in basic and advanced SQL operations, such as CRUD, complex queries and window functions. 4. Master debugging skills and use the EXPLAIN command to optimize query performance. 5. Overcome learning challenges through practice, utilizing learning resources, attaching importance to performance optimization and maintaining curiosity.

SQL and Databases: A Perfect PartnershipSQL and Databases: A Perfect PartnershipApr 25, 2025 am 12:04 AM

The relationship between SQL and database is closely integrated, and SQL is a tool for managing and operating databases. 1.SQL is a declarative language used for data definition, operation, query and control. 2. The database engine parses SQL statements and executes query plans. 3. Basic usage includes creating tables, inserting and querying data. 4. Advanced usage involves complex queries and subqueries. 5. Common errors include syntax, logic and performance issues, which can be debugged through syntax checking and EXPLAIN commands. 6. Optimization techniques include using indexes, avoiding full table scanning and optimizing queries.

SQL vs. MySQL: Clarifying the Relationship Between the TwoSQL vs. MySQL: Clarifying the Relationship Between the TwoApr 24, 2025 am 12:02 AM

SQL is a standard language for managing relational databases, while MySQL is a database management system that uses SQL. SQL defines ways to interact with a database, including CRUD operations, while MySQL implements the SQL standard and provides additional features such as stored procedures and triggers.

The Importance of SQL: Data Management in the Digital AgeThe Importance of SQL: Data Management in the Digital AgeApr 23, 2025 am 12:01 AM

SQL's role in data management is to efficiently process and analyze data through query, insert, update and delete operations. 1.SQL is a declarative language that allows users to talk to databases in a structured way. 2. Usage examples include basic SELECT queries and advanced JOIN operations. 3. Common errors such as forgetting the WHERE clause or misusing JOIN, you can debug through the EXPLAIN command. 4. Performance optimization involves the use of indexes and following best practices such as code readability and maintainability.

Getting Started with SQL: Essential Concepts and SkillsGetting Started with SQL: Essential Concepts and SkillsApr 22, 2025 am 12:01 AM

SQL is a language used to manage and operate relational databases. 1. Create a table: Use CREATETABLE statements, such as CREATETABLEusers(idINTPRIMARYKEY, nameVARCHAR(100), emailVARCHAR(100)); 2. Insert, update, and delete data: Use INSERTINTO, UPDATE, DELETE statements, such as INSERTINTOusers(id, name, email)VALUES(1,'JohnDoe','john@example.com'); 3. Query data: Use SELECT statements, such as SELEC

SQL: The Language, MySQL: The Database Management SystemSQL: The Language, MySQL: The Database Management SystemApr 21, 2025 am 12:05 AM

The relationship between SQL and MySQL is: SQL is a language used to manage and operate databases, while MySQL is a database management system that supports SQL. 1.SQL allows CRUD operations and advanced queries of data. 2.MySQL provides indexing, transactions and locking mechanisms to improve performance and security. 3. Optimizing MySQL performance requires attention to query optimization, database design and monitoring and maintenance.

What SQL Does: Managing and Manipulating DataWhat SQL Does: Managing and Manipulating DataApr 20, 2025 am 12:02 AM

SQL is used for database management and data operations, and its core functions include CRUD operations, complex queries and optimization strategies. 1) CRUD operation: Use INSERTINTO to create data, SELECT reads data, UPDATE updates data, and DELETE deletes data. 2) Complex query: Process complex data through GROUPBY and HAVING clauses. 3) Optimization strategy: Use indexes, avoid full table scanning, optimize JOIN operations and paging queries to improve performance.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!