本文的几种分页方法摘自《SQL Server2005性能调优》一书。用以下脚本生成测试数据:
CREATE
TABLE
TRANS_TABLE(
MYID INT IDENTITY ( 1 , 1 ) NOT NULL PRIMARY KEY ,
MYDESC VARCHAR ( 10 ),
MYDATE DATETIME ,
MYGROUPID INT )
DECLARE @I INT
SET @I = 0
WHILE @I < 1000000
BEGIN
INSERT INTO TRANS_TABLE
SELECT CHAR ( ASCII ( ' A ' ) - 2 + ( 2 * ( 1 + ABS (CHECKSUM( NEWID ())) % 26 ))),
DATEADD ( day , ABS (CHECKSUM( NEWID ())) % 365 , ' 01/01/2007 ' ),
( ABS (CHECKSUM( NEWID ())) % 10 )
SET @I = @I + 1
END
CREATE NONCLUSTERED INDEX IX_TRANS_TABLE_MYDATE
ON TRANS_TABLE(MYDATE)
CREATE NONCLUSTERED INDEX IX_TRANS_TABLE_MYGROUPID
ON TRANS_TABLE(MYGROUPID)
MYID INT IDENTITY ( 1 , 1 ) NOT NULL PRIMARY KEY ,
MYDESC VARCHAR ( 10 ),
MYDATE DATETIME ,
MYGROUPID INT )
DECLARE @I INT
SET @I = 0
WHILE @I < 1000000
BEGIN
INSERT INTO TRANS_TABLE
SELECT CHAR ( ASCII ( ' A ' ) - 2 + ( 2 * ( 1 + ABS (CHECKSUM( NEWID ())) % 26 ))),
DATEADD ( day , ABS (CHECKSUM( NEWID ())) % 365 , ' 01/01/2007 ' ),
( ABS (CHECKSUM( NEWID ())) % 10 )
SET @I = @I + 1
END
CREATE NONCLUSTERED INDEX IX_TRANS_TABLE_MYDATE
ON TRANS_TABLE(MYDATE)
CREATE NONCLUSTERED INDEX IX_TRANS_TABLE_MYGROUPID
ON TRANS_TABLE(MYGROUPID)
1、 基于CTE分页
1)、用row_number()排名函数,派生表的方式分页
DECLARE
@START_ID
int
,
@START_ROW
int
,
@MAX_ROWS
int
SELECT @START_ROW = 1 , @MAX_ROWS = 25
select *
from ( select p. * , rownum rnum
FROM (
SELECT ROW_NUMBER() OVER ( ORDER BY MyDate, MYID) AS rowNum, *
FROM TRANS_TABLE (NOLOCK)
) p
where rownum <= @START_ROW + @MAX_ROWS - 1
) z
where rnum >= @START_ROW
SELECT @START_ROW = 1 , @MAX_ROWS = 25
select *
from ( select p. * , rownum rnum
FROM (
SELECT ROW_NUMBER() OVER ( ORDER BY MyDate, MYID) AS rowNum, *
FROM TRANS_TABLE (NOLOCK)
) p
where rownum <= @START_ROW + @MAX_ROWS - 1
) z
where rnum >= @START_ROW
2)、用CTE方式取代派生表
DECLARE
@START_ROW
int
,
@MAX_ROWS
int
,
@TOT_ROW_CNT
int
SELECT @START_ROW = 1 , @MAX_ROWS = 25 ;
WITH PAGED AS (
SELECT ROW_NUMBER() OVER ( ORDER BY MyDate, MYID) AS rowNum, *
FROM TRANS_TABLE (NOLOCK)
)
SELECT *
FROM PAGED
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1
SELECT @START_ROW = 1 , @MAX_ROWS = 25 ;
WITH PAGED AS (
SELECT ROW_NUMBER() OVER ( ORDER BY MyDate, MYID) AS rowNum, *
FROM TRANS_TABLE (NOLOCK)
)
SELECT *
FROM PAGED
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1
3)、也是CTE方法,但是根据测试数据显示这种性能比前两种都好
DECLARE
@START_ROW
int
,
@MAX_ROWS
int
,
@TOT_ROW_CNT
int
SELECT @START_ROW = 1 , @MAX_ROWS = 25 ;
WITH PAGED AS (
SELECT ROW_NUMBER() OVER ( ORDER BY MyDate, MYID) AS rowNum, MYID
FROM TRANS_TABLE (NOLOCK)
)
SELECT TT. *
FROM PAGED PGD
INNER JOIN TRANS_TABLE TT
ON PGD.MYID = TT.MYID
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1
ORDER BY MyDate, MYID
SELECT @START_ROW = 1 , @MAX_ROWS = 25 ;
WITH PAGED AS (
SELECT ROW_NUMBER() OVER ( ORDER BY MyDate, MYID) AS rowNum, MYID
FROM TRANS_TABLE (NOLOCK)
)
SELECT TT. *
FROM PAGED PGD
INNER JOIN TRANS_TABLE TT
ON PGD.MYID = TT.MYID
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1
ORDER BY MyDate, MYID
2、 基于ROW_COUNT的分页
DECLARE
@START_ID
int
,
@START_ROW
int
,
@MAX_ROWS
int
,
@START_DATETIME DATETIME , @TOT_ROW_CNT INT
SELECT @START_ROW = 1 , @MAX_ROWS = 25
-- Get the first row for the page
SET ROWCOUNT @START_ROW
SELECT @START_ID = MYID, @START_DATETIME = MYDATE FROM TRANS_TABLE (NOLOCK)
ORDER BY MYDATE, MYID
-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @MAX_ROWS
SELECT *
FROM TRANS_TABLE (NOLOCK)
WHERE MYID >= @START_ROW
AND MYDATE >= @START_DATETIME
ORDER BY MYDATE, MYID
SET ROWCOUNT 0
@START_DATETIME DATETIME , @TOT_ROW_CNT INT
SELECT @START_ROW = 1 , @MAX_ROWS = 25
-- Get the first row for the page
SET ROWCOUNT @START_ROW
SELECT @START_ID = MYID, @START_DATETIME = MYDATE FROM TRANS_TABLE (NOLOCK)
ORDER BY MYDATE, MYID
-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @MAX_ROWS
SELECT *
FROM TRANS_TABLE (NOLOCK)
WHERE MYID >= @START_ROW
AND MYDATE >= @START_DATETIME
ORDER BY MYDATE, MYID
SET ROWCOUNT 0
3、 TOP @X分页
SQL Server 2005中可以把返回行数做为参数传给top语句。
DECLARE
@START_ID
int
,
@START_ROW
int
,
@MAX_ROWS
int
,
@TOT_ROW_CNT
INT
,
@START_DESC
VARCHAR
(
10
)
SELECT @START_ROW = 1 , @MAX_ROWS = 25
-- Get the first row for the page
SELECT TOP ( @START_ROW ) @START_ID = MYID, @START_DESC = MYDESC FROM TRANS_TABLE (NOLOCK)
ORDER BY MYDESC, MYID
SELECT TOP ( @MAX_ROWS ) *
FROM TRANS_TABLE (NOLOCK)
WHERE MYID >= @START_ROW
AND MYDESC >= @START_DESC
ORDER BY MYDESC, MYID
SELECT @START_ROW = 1 , @MAX_ROWS = 25
-- Get the first row for the page
SELECT TOP ( @START_ROW ) @START_ID = MYID, @START_DESC = MYDESC FROM TRANS_TABLE (NOLOCK)
ORDER BY MYDESC, MYID
SELECT TOP ( @MAX_ROWS ) *
FROM TRANS_TABLE (NOLOCK)
WHERE MYID >= @START_ROW
AND MYDESC >= @START_DESC
ORDER BY MYDESC, MYID
4、 Temp表分页
DECLARE
@START_ROW
int
,
@MAX_ROWS
int
,
@TOT_ROW_CNT
int
SELECT @START_ROW = 1 , @MAX_ROWS = 25 ;
SELECT ROW_NUMBER() OVER ( ORDER BY MyDate, MYID) AS rowNum,
MYID
into # TEMP
FROM TRANS_TABLE (NOLOCK)
SELECT TT. *
FROM TRANS_TABLE (NOLOCK) TT
INNER JOIN # TEMP T
ON TT.MYID = T.MYID
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1
DROP TABLE # TEMP
SELECT @START_ROW = 1 , @MAX_ROWS = 25 ;
SELECT ROW_NUMBER() OVER ( ORDER BY MyDate, MYID) AS rowNum,
MYID
into # TEMP
FROM TRANS_TABLE (NOLOCK)
SELECT TT. *
FROM TRANS_TABLE (NOLOCK) TT
INNER JOIN # TEMP T
ON TT.MYID = T.MYID
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1
DROP TABLE # TEMP