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
插入数据：
(2
万条，用更多的数据测试会明显一些
)
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
-------------------------------------
分页方案一：
(
利用
Not In
和
SELECT TOP
分页
)
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 20 id
FROM TestTable
ORDER BY id))
ORDER BY ID
SELECT TOP
页大小
*
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP
页大小
*
页数
id
FROM
表
ORDER BY id))
ORDER BY ID
-------------------------------------
分页方案二：
(
利用
ID
大于多少和
SELECT TOP
分页）
语句形式：
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
页大小
*
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP
页大小
*
页数
id
FROM
表
ORDER BY id) AS T))
ORDER BY ID
-------------------------------------
分页方案三：
(
利用
SQL
的游标存储过程分页
)
create procedure XiaoZhengGe
@
sql
str nvarchar(4000), --
查询
字符
串
@currentpage int, --
第
N
页
@pagesize int --
每页行数
as
set nocount on
declare @P1 int, --P1
是游标的
id
@rowcount int
exec sp_cursoropen @P1 output,@
sql
str,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(
1.0*@rowcount/@pagesize
) as
总页数
--,@rowcount as
总行数
,@currentpage as
当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off
其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。
建议优化的时候，加上主键和索引，查询效率会提高。
通过
SQL
查询分析器，显示比较：我的结论是
:
分页方案二：
(
利用
ID
大于多少和
SELECT TOP
分页）效率最高，需要拼接
SQL
语句
分页方案一：
(
利用
Not In
和
SELECT TOP
分页
)
效率次之，需要拼接
SQL
语句
分页方案三：
(
利用
SQL
的游标存储过程分页
)
效率最差，但是最为通用
在实际情况中，要具体分析。
以下是另一个存储过程的例子.
通常用普通的
SQL
语句查询数据，在数据量少的情况下速度上没什么感觉，一旦数居量很大之后，速度会明显变慢，因为大量的数据在网络中传输会花掉不少的时间，如果使用了分页查询数据，只反回有用的数据，把不需要的数据排除后，传输速度会快很多，如下是一个
SQL
分页的存储过程，本过程是以
SQL
自带的
Northwind
数据库为例写的，可以根据自已的需要随意更改
CREATE
PROCEDURE
[GetCustomersDataPage]
@PageIndex
INT
,
--
页号
@PageSize
INT
,
--
页数
@RecordCount
INT
OUT
,
--
记录数
@PageCount
INT
OUT
--
页数
AS
SELECT
@RecordCount =
COUNT
(*)
FROM
Customers
SET
@PageCount =
CEILING
(@RecordCount * 1.0 / @PageSize)
DECLARE
@SQLSTR
NVARCHAR
(1000)
IF
@PageIndex = 0
OR
@PageCount <= 1
SET
@SQLSTR =
'SELECT
TOP
'
+
STR
( @PageSize )+
' CustomerID, CompanyName,Address,Phone FROM Customers ORDER BY CustomerID DESC'
ELSE
IF
@PageIndex = @PageCount - 1
SET
@SQLSTR =
' SELECT * FROM ( SELECT
TOP
'
+
STR
( @RecordCount - @PageSize * @PageIndex )+
' CustomerID, CompanyName,Address,Phone FROM Customers ORDER BY CustomerID ASC ) TempTable ORDER BY CustomerID DESC'
ELSE
SET
@SQLSTR =
' SELECT
TOP
'
+
STR
( @PageSize )+
' * FROM ( SELECT
TOP
'
+
STR
( @RecordCount - @PageSize * @PageIndex )+
' CustomerID, CompanyName,Address,Phone FROM Customers ORDER BY CustomerID ASC ) TempTable ORDER BY CustomerID DESC'
EXEC
(@SQLSTR)
GO
