浅谈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  

以上便是这次为您介绍的 SQL Server 2005中几种分页方法，希望对您学习SQL Server分页查询方面能有所帮助。

原文链接：http://www.cnblogs.com/qiuwuyu/archive/2011/03/21/1989870.html