Establish 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,000, more data testing 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
-------------------------------------
Page 1: (Util IN and SELECT TOP Patement)
Statement form:
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 number ID
FROM table
Order by id))
ORDER BY ID
-------------------------------------
Page 2: (Using ID greater than how much and select TOP paging)
Statement form:
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 number ID
FROM table
Order By ID) AS T))
ORDER BY ID
-------------------------------------
Page 3: (using SQL cursor storage 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 outputselect ceiling (1.0 * @ rowcount / @ pagesize) as total number of pages -, @ rowcount as 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 you can do it three, but the efficiency will be low.
When it is recommended, add the primary key and index, and the query efficiency will increase.
Search the analyzer via SQL, show comparison: My conclusion is:
Page 2: (Using ID greater than how much and select TOP paging) The highest efficiency, need to splicing SQL statements
Paging Scheme 1: (Using Not in and SELECT TOP Patement) Efficiency, you need to splicing SQL statements
Page three: (using SQL cursor storage procedure paging) Efficiency is the worst, but most common
In the actual situation, it is necessary to analyze.
Transfer from:
http://goaler.xicp.net/showlog.asp?id=502
