Split-page scenarios for SQL Server stored procedures compete

server| Stored Procedures | paging
SQL Server Stored procedure paging, this issue has been discussed for several years, a lot of friends are asking me, so I would like to post my point of view
Create a table:

CREATE TABLE [TestTable] (
[ID] [int] IDENTITY (1, 1) not NULL,
[FirstName] [nvarchar] (MB) COLLATE chinese_prc_ci_as NULL,
[LastName] [nvarchar] (MB) COLLATE chinese_prc_ci_as NULL,
[Country] [nvarchar] (m) COLLATE chinese_prc_ci_as NULL,
[Note] [nvarchar] (Watts) COLLATE chinese_prc_ci_as NULL
) on [PRIMARY]
Go



Insert data: (20,000, test with more data will be obvious)
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 Scheme one: (using not in and select top pagination)
Statement form:
SELECT Top 10 *
From TestTable
WHERE (ID not in
(SELECT Top ID
From TestTable
Order by ID)
ORDER BY ID


SELECT Top Page Size *
From TestTable
WHERE (ID not in
(SELECT top Page size * Pages ID
From table
Order by ID)
ORDER BY ID

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

Paging Scheme two: (with ID greater than how much and select top pagination)
Statement form:
SELECT Top 10 *
From TestTable
WHERE (ID >
(SELECT MAX (ID)
From (SELECT the top 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 * Pages ID
From table
Order by ID) as T)
ORDER BY ID


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

Paging Scenario Three: (using SQL Cursor stored procedure paging)
CREATE PROCEDURE Xiaozhengge
@sqlstr nvarchar (4000),--query string
@currentpage int,--nth page
@pagesize INT--Number of 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
Select Ceiling (1.0* @rowcount/@pagesize) as total number of pages--, @rowcount as rows, @currentpage as current page
Set @currentpage = (@currentpage-1) * @pagesize +1
exec sp_cursorfetch @P1, @currentpage, @pagesize
EXEC sp_cursorclose @P1
SET NOCOUNT OFF

Other scenarios: If you don't have a primary key, you can use a temporary table, or you can do it with a scenario, but it's inefficient.
When optimization is recommended, the query efficiency is increased by adding primary keys and indexes.

Display comparisons through SQL Query Analyzer: My conclusion is:
Paging Scheme two: (with ID greater than the number and select top paging) The most efficient, need to stitch SQL statements
Paging Scheme one: (Use not in and select top paging) efficiency second, need to stitch SQL statements
Paging Scenario Three: (using SQL Cursor stored procedure paging) is the least efficient, but most versatile

In the actual situation, to be specific analysis.


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