SQL Server でクエリー結果をページングする方法

SQL Server でクエリー結果をページングする

ウエブシステム等の開発をしていると、検索結果をページに分けて表示するために、ページング処理が必要になってくることがあると思います。

MySQL では LIMIT を使えば簡単にできるページングですが、 SQL Server には LIMIT がありません。

今回は SQL Server でどのようにクエリー結果を効率よくページングすることができるのかをご紹介します。

ORDER BY 句で OFFSET と FETCH を使う方法

もし、SQL Server のバージョンが 2012 以降なのであれば、一番効率の良い方法は SELECT ステートメントの ORDER BY 句で OFFSET と FETCH を使う方法です。

構文は次の通りです。

ORDER BY [通常の ORDER BY 句と同様にソート順を指定する]
   OFFSET [スキップするレコードの数] ROWS  
   FETCH NEXT [取得するレコードの数] ROWS ONLY;

それでは実際に使ってみましょう。次のような Person テーブルがあります。

このクエリー結果を、各ページ 10 レコードずつ表示するとして、3 ページ目に表示される結果を取得したい場合は次のように取得することができます。

DECLARE @NumberOfRowsPerPage INT = 10,
	@PageNumber INT = 3;

DECLARE @OffsetRowCount INT = (@PageNumber - 1) * @NumberOfRowsPerPage;

SELECT	PersonID,
	FirstName,
	MiddleName,
	LastName
FROM	Person
ORDER BY PersonID
   OFFSET @OffsetRowCount ROWS  
   FETCH NEXT @NumberOfRowsPerPage ROWS ONLY;

実際に使う時は検索条件を WHERE 句で指定したり、名前などでソートしたりするかと思いますが、今回は正しく取得されていることがわかるようにフィルターはせず、行番号と同じ値にしてある PersonID でソートしています。

OFFSET に続いてスキップしたいレコード数を [3 - 1 ページ分] x [1 ページのレコード数: 10] を計算して指定して、FETCH NEXT に続いて取得したいレコード数 [1 ページのレコード数: 10] を指定しています。

ROW_NUMBER() とサブクエリーを使う方法

SQL Server 2012 より前バージョンで SQL Server 2005 以降では ROW_NUMBER() とサブクエリーを使って、ページングをすることができます。

まず ROW_NUMBER() とサブクエリーを使って、各ページ 10 レコードずつ表示するとして、3 ページ目に表示される結果を取得するクエリーは以下の通りです。

DECLARE @NumberOfRowsPerPage INT = 10,
	@PageNumber INT = 3;

DECLARE	@StartRowNo INT = ((@PageNumber - 1) * @NumberOfRowsPerPage) + 1,
	@EndRowNo   INT = @PageNumber * @NumberOfRowsPerPage;
		
SELECT	PersonID,
	FirstName,
	MiddleName,
	LastName  
FROM 
    (SELECT ROW_NUMBER() OVER (ORDER BY PersonID) AS RowNo,
	    PersonID,
	    FirstName,
	    MiddleName,
	    LastName  
     FROM   Person) AS P
WHERE	RowNo BETWEEN @StartRowNo AND @EndRowNo
ORDER BY PersonID;

先に、開始の @StartRowNo と終了の @EndRowNo を計算しておき、サブクエリーの中で ROW_NUMBER() で取得したいソート順で RowNo を振り WHERE 句でフィルターしています。

また、ほぼ同じような方法で、サブクエリーの代わりに共通テーブル式（CTE）を使っても取得可能です。

SQL Server でのページングについては、方法とパフォーマンスを比較した記事が多数あります。

だいたいの結果としては、ROW_NUMBER() & サブクエリーと、ROW_NUMBER() & 共通テーブル式（CTE）はいい勝負、使えるなら OFFSET & FETCH を使っておけば間違いないようです。

特にクエリー結果の返すレコード数が多い時は OFFSET & FETCH のパフォーマンスが良く、その他の差が大きく開いていました。

ご参考になれば幸いです！

SQL Server でクエリー結果をページングする方法