Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.