SQL Server - OFFSET 句と FETCH 句

SQL Server 2012 からですが、ORDER BY 句に OFFSET 句と FETCH 句が追加されています。クエリだけでページングを実現できるので素敵ですね。

ということで試してみたのでクエリなどを残しておきます。

まずクエリはこんな感じです。

select *
from テーブル名
order by カラム名
    offset @offset /* オフセットする行数 */ rows
    fetch next @count /* 取得する行数 */ rows only;

@offset にはオフセット（スキップ）する行数、@count には取得する行数を指定します。

ORDER BY 句 (Transact-SQL)

実際にページングで使うときは、それぞれ取得するページインデックスとページサイズ（1ページあたりの件数）から計算することになると思いますがそのあたりは省略。

MSDN に FETCH 句でサブクエリを使ってページサイズを取得するサンプルも載っています。なるほど。

テストデータの準備

ではちょっと試してみましょう。

まずはテストデータとして1～50までの連番を生成します。共通テーブル式を使っています。

-- シーケンスのソース（共通テーブル式を使って1～50までの連番生成）
with Source(Value) as(
    select 1
    union all
    select Value + 1
    from Source
)
select top 50 *
into Sequence
from Source;

/*
Value
-----------
1
2
3
--（省略）
48
49
50
*/

取得してみる

20までスキップして、21から30までの10個を取得してみます。

-- 20までスキップして、21から30までの10個を取得
select *
from Sequence
order by Value
    offset 20 rows         -- 20個スキップして
    fetch next 10 rows only;   -- 10個取得
/*
Value
-----------
21
22
23
24
25
26
27
28
29
30
*/

46から10個取得してみます。50までしかないので46～50までの5個取得されます。

-- 46から10個を取得
select *
from Sequence
order by Value
    offset 45 rows
    fetch next 10 rows only;

/*
Value
-----------
46
47
48
49
50
*/

OFFSET 句には0以上、FETCH 句には1以上の整数を指定する必要があります。それぞれ、負の値、0以下の値を指定するとエラーになります。

-- OFFSET 句に負の値を指定するとエラー
select *
from Sequence
order by Value
    offset -1 rows
    fetch next 2 rows only;
/*
OFFSET 句のオフセットに負の値を指定することはできません。
*/

-- FETCH 句に0以下の値を指定するとエラー
select *
from Sequence
order by Value
    offset 0 rows
    fetch next 0 rows only;
/*
FETCH 句の行数には 0 より大きい値を指定する必要があります。
*/

また OFFSET 句は省略できませんが FETCH 句は省略できます。

-- FETCH 句は省略できる
select *
from Sequence
order by Value
    offset 45 rows

/*
Value
-----------
46
47
48
49
50
*/