SQL Server分頁(yè)查詢是我們經(jīng)常會(huì)用到的功能，其實(shí)現(xiàn)方法也有很多，本文的幾種分頁(yè)方法摘自《SQL Server2005性能調(diào)優(yōu)》一書。希望對(duì)您學(xué)習(xí)SQL Server分頁(yè)查詢方面能有所幫助。

用以下腳本生成測(cè)試數(shù)據(jù)：

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分頁(yè)

1）用row_number()排名函數(shù)，派生表的方式分頁(yè)

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方法，但是根據(jù)測(cè)試數(shù)據(jù)顯示這種性能比前兩種都好

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的分頁(yè)

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分頁(yè)

SQL Server 2005中可以把返回行數(shù)做為參數(shù)傳給top語(yǔ)句。

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表分頁(yè)

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中幾種分頁(yè)方法，希望對(duì)您學(xué)習(xí)SQL Server分頁(yè)查詢方面能有所幫助。

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

【編輯推薦】

1. Access分頁(yè)方案
2. 淺談如何構(gòu)建高效的MySQL分頁(yè)
3. Oracle分頁(yè)查詢語(yǔ)句的寫法
4. Oracle、SQL和DB2分頁(yè)查詢寫法介紹