Paging of SQL Server Stored Procedure

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 share my opinion on creating a table here: CREATETABLE [TestTable] ([ID] [int] IDENTITY (100) NOTNULL, [FirstName] [nvarchar] () COLLATEChinese_PRC_CI_ASNULL, [LastName]

The paging of the SQL Server Stored Procedure has been discussed for several years. Many friends are asking me, so I would like to share my opinion on creating a table here: create table [TestTable] ([ID] [int] IDENTITY (1, 1) not null, [FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL, [LastName]

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

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

Possible use:

Declare @ a int
Set @ a = 1002

Exec ('select top '+ @ a + 'id from testtable ')

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.