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,000 items, testing with more data will be more 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 solution one: (Use Not In and SELECT TOP paging)
Statement form:
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 20 ids
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
---------------------------------------
Paging scheme two: (Use the sum of how much ID is greater than 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
---------------------------------------
Paging scheme three: (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 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 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 you can use solution three, but the efficiency will be low.
It is recommended that when optimizing, adding primary keys and indexes will improve query efficiency.
Through SQL Query Analyzer, display comparison: My conclusion is:
Paging scheme two: (Using ID greater than what and SELECT TOP for paging) The most efficient, need to splice SQL statements paging scheme one: (Using Not In and SELECT TOP for paging) The second most efficient, need to splice SQL statements for paging scheme three: (Using SQL's paging scheme) Cursor stored procedure paging) is the least efficient, but the most common
. In actual situations, specific analysis is required.