server| Stored Procedures | Paging SQL Server Stored procedures, this issue has been discussed for several years, many friends are asking me, so here is my point of view
set up the table:
CREATE TABLE [testtable] (
[ID] [int] IDENTITY (1, 1) not NULL,
[FirstName] [nvarchar] (m) COLLATE chinese_prc_ci_as NULL,
[LastName] [nvarchar] (m) 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: (Use not in and select top paging)
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
-------------------------------------
page Scheme two: (with ID greater than how much and select top paging)
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 Scheme III: (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 Solutions: If you do not have a primary key, you can use a temporary table, but also can be used to do three, but inefficient.
recommend optimization, add primary key and index, query efficiency will improve.
through SQL Query Analyzer, show comparison: My conclusion is:
Paging Scheme two: (with ID greater than the number and select top paging) The most efficient, the need to splice SQL statements
Paging Scheme one: (Use not in and select top paging) efficiency second, need to splice SQL statements
Paging Scheme III: (using SQL Cursor stored procedure paging) is the least efficient, but the most common
in the actual situation, to be specific analysis.
more discussion See:
http://community.csdn.net/Expert/topic/3292/3292678.xml?temp=.1621515
