SQL Server 2005のいくつかのページング方法について簡単に説明します

SQL Serverページングクエリは、私たちがよく使用する機能であり、その実現方法もたくさんあります.本稿のいくつかのページング方法は、『SQL Server 2005パフォーマンスチューニング』から抜粋しています.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メソッドでもあるが,テストデータによると,この性能は前の2つよりも優れていることが分かった.

  
  
  
  

   
   
   
   
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ページングクエリーの学習に役立つと思います.