SQL Server2005的几种分页方法

本文的几种分页方法摘自《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)

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

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

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

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

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

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