Comparison of paging solutions for SQL Server Stored Procedures [Reprinted from Li honggen blogs]

Source: Internet
Author: User

The paging of the SQL Server Stored Procedure has been discussed for several years. Many friends are asking me, so I would like to express my point of view here.
Create a table:

Create Table [testtable] (
[ID] [int] identity (1, 1) not null,
[Firstname] [nvarchar] (100) Collate chinese_prc_ci_as null,
[Lastname] [nvarchar] (100) Collate chinese_prc_ci_as null,
[Country] [nvarchar] (50) Collate chinese_prc_ci_as null,
[Note] [nvarchar] (2000) Collate chinese_prc_ci_as null
) On [primary]
Go

 

Insert data: (20 thousand, more data will be used for testing)
Set identity_insert testtable on

Declare @ I int

Declare @ I int
Set @ I = 1
While I <= 20000
Begin
Insert into testtable ([ID], firstname, lastname, country, note) values (@ I, 'firstname _ XXX', 'lastname _ XXX', 'country _ XXX ', 'note _ XXX ')
Set @ I = @ I + 1
End

Set identity_insert testtable off

 

-------------------------------------

Paging solution 1: (use not in and select top pages)
Statement format:
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 * Page ID
From table
Order by ID ))
Order by ID

-------------------------------------

Paging solution 2: (use the ID greater than the number and select top pages)
Statement format:
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 * Page ID
From table
Order by ID) as t ))
Order by ID

-------------------------------------

Paging solution 3: (using SQL cursor Stored Procedure paging)
Create procedure xiaozhengge
@ Sqlstr nvarchar (4000), -- query string
@ Currentpage int, -- page n
@ Pagesize int -- number of lines per page
As
Set nocount on
Declare @ P1 int, -- P1 is the cursor ID
@ Rowcount int
Exec sp_cursoropen @ P1 output, @ sqlstr, @ scrolopt = 1, @ ccopt = 1, @ rowcount = @ rowcount output
Select ceiling (1.0 * @ rowcount/@ pagesize) as total number of pages --, @ rowcount as total number of rows, @ currentpage as current page
Set @ currentpage = (@ currentpage-1) * @ pagesize + 1
Exec sp_cursorfetch @ P1, 16, @ currentpage, @ pagesize
Exec sp_cursorclose @ p1
Set nocount off

Other solutions: If there is no primary key, you can use a temporary table or solution 3, but the efficiency is low.
We recommend that you add primary keys and indexes during optimization to improve query efficiency.

The SQL query Analyzer displays a comparison: My conclusion is:
Paging solution 2: (using more than ID and select top pages) is the most efficient. You need to splice an SQL statement
Paging solution 1: (using not in and select top pages) The efficiency is second, and SQL statements need to be spliced.
Paging solution 3: (using SQL cursor Stored Procedure paging) The efficiency is the worst, but the most common

In actual situations, specific analysis is required.

For more information, see:
http://community.csdn.net/Expert/topic/3292/3292678.xml? Temp =. 1621515

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.