How to realize query pagination in MSSQLServer
This article is to share with you about how to achieve query paging in MSSQLServer, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.
Select top 10 * from yc_module where (block_id not in (select top 0 block_id from yc_module order by block_id)) order by block_id
Paging scheme 1: (paging using Not In and SELECT TOP)
Statement form:
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 20 id
FROM TestTable
ORDER BY id))
ORDER BY ID
SELECT TOP page size *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP page size * number of pages id
FROM table
ORDER BY id))
ORDER BY ID
-
Paging scheme 2: (using ID greater than how much and SELECT TOP paging)
Statement form:
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
(SELECT MAX (id)
FROM (SELECT TOP 20 id
FROM TestTable
ORDER BY id) AS T))
ORDER BY ID
SELECT TOP page size *
FROM TestTable
WHERE (ID >
(SELECT MAX (id)
FROM (SELECT TOP page size * number of pages id
FROM table
ORDER BY id) AS T))
ORDER BY ID
-
Paging scheme 3: (paging using the cursor stored procedure of SQL)
Create procedure XiaoZhengGe
@ sqlstr nvarchar (4000),-- query string
@ currentpage int,-- page N
@ pagesize int-rows per page
As
Set nocount on
Declare @ P1 int,-P1 is the id of the cursor
@ rowcount int
Exec sp_cursoropen @ P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
Total number of select ceiling (1.0*@rowcount/@pagesize) as pages-- @ rowcount as total rows, @ currentpage as current page
Set @ currentpage= (@ currentpage-1) * @ pagesize+1
Exec sp_cursorfetch @ P1Magol 16 recordscurrentpageMorepageSize
Exec sp_cursorclose @ P1
Set nocount off
Other options: if there is no primary key, you can use a temporary table, or you can use option 3, but it will be inefficient.
It is recommended that when optimizing, add the primary key and index, the query efficiency will be improved.
Through the SQL query Analyzer, display the comparison: my conclusion is:
Paging scheme 2: (using ID greater than how much and SELECT TOP paging) is the most efficient and needs to concatenate SQL statements
Paging scheme 1: (using Not In and SELECT TOP paging) the efficiency is the second, and the SQL statements need to be spliced.
Paging scheme 3: (paging using SQL's cursor stored procedures) is the least efficient, but the most general
The above is how to achieve query paging in MSSQLServer. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.
