浅谈SQL Server2005的几种分页方法

SQL Server分页查询是我们经常会用到的功能，其实现方法也有很多，本文的几种分页方法摘自《SQL Server2005性能调优》一书。希望对您学习SQL Server分页查询方面能有所帮助。

用以下脚本生成测试数据：

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 = 0WHILE @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 PAGEDWHERE 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_idSET 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 TON TT.MYID = T.MYID  
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1  
DROP TABLE #TEMP