Paging of SQL Server Stored Procedure

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 ')