几种分页查询的实现与比较

原贴地址：http://blog.csdn.net/smartkernel/archive/2008/02/22/2112917.aspx

www.SmartKernel.com

几种分页查询的实现与比较：个人比较推荐第5种方式，性能比较稳定，语法结构简单

--【00】初始化数据（执行几分钟就可以产生几百万数据）Person表结构：ID（int 自增列）,Name(nvarchar(50)),Age(int)
WHILE 1 = 1
BEGIN
INSERT INTO dbo.Person (Name,Age) VALUES (NEWID(),Round(Rand()*100,1))
END

SELECT * FROM dbo.Person

--【01】200万数据情况下，测试时用了7秒：临时表的方法
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 100
SET @EndRow = 110
SET ROWCOUNT 0
DECLARE @TempTable TABLE
(
ID int IDENTITY PRIMARY KEY,
PK int
)

INSERT INTO @TempTable SELECT ID FROM dbo.Person ORDER BY Name

SELECT A.* FROM Person AS A JOIN @TempTable AS B ON A.ID = B.PK WHERE B.ID >= @StartRow AND B.ID < @EndRow

--【02】200万数据情况下，测试时用了4秒：影响行数方法
DECLARE @StartRow INT
DECLARE @PageSize INT
SET @StartRow = 100
SET @PageSize = 10

DECLARE @Sort NVARCHAR(50)--与ORDER BY的列类型一致
SET ROWCOUNT @StartRow

SELECT @Sort = Name FROM Person ORDER BY Name
SET ROWCOUNT @PageSize

SELECT * FROM Person WHERE Name >= @Sort ORDER BY Name

--【03】200万数据情况下，测试时用了0秒：子查询的方法
DECLARE @StartRow INT
DECLARE @PageSize INT
SET ROWCOUNT 0
--SET @StartRow = 100（TOP关键字不能使用变量）
--SET @PageSize = 10（TOP关键字不能使用变量）
SELECT * FROM Person WHERE ID IN
(
SELECT TOP 10 ID FROM Person WHERE ID NOT IN(SELECT TOP 100 ID FROM Person ORDER BY Name)
ORDER BY Name
)
ORDER BY Name

--【04】200万数据情况下，测试时用了5秒：游标的方法
DECLARE @StartRow INT
DECLARE @PageSize INT
SET @StartRow = 100
SET @PageSize = 10
SET ROWCOUNT 0

DECLARE @ID INT
DECLARE @TempTable TABLE (ID INT NOT NULL PRIMARY KEY)

DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT ID FROM Person ORDER BY Name

OPEN PagingCursor

FETCH RELATIVE @StartRow FROM PagingCursor INTO @ID

WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
INSERT INTO @TempTable(ID) VALUES(@ID)
FETCH NEXT FROM PagingCursor INTO @ID
SET @PageSize = @PageSize - 1
END

CLOSE PagingCursor
DEALLOCATE PagingCursor

SELECT A.* FROM Person AS A JOIN @TempTable AS B ON A.ID = B.ID ORDER BY Name

--【05】200万数据情况下，测试时用了1秒：行号函数的方法（只有SQL Server 2005以上版本支持）
DECLARE @StartRow INT
DECLARE @PageSize INT
SET ROWCOUNT 0
SET @StartRow = 100
SET @PageSize = 10

SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY Name) AS RowNumber,* FROM Person
) AS A WHERE A.RowNumber BETWEEN @StartRow + 1 AND @StartRow + @PageSize