- オフセット
- Fetch FirstまたはFetch Next（ 最初を取得する...または次を取得する... ）
オフセット
このコマンドを使用すると、クエリ結果を表示する前に、指定した行数をスキップできます。 これが何を意味するのかというと、テーブルに100個のレコードがあり、最初の10行をスキップして11〜100行目を印刷する必要があるとします。これは次のクエリで簡単に解決できます。
Select * From <SomeTable> Order by <SomeColumn> Offset 10 Rows
.Netを実践している同志にとって、指定された行数をスキップするSkipコレクションの拡張メソッドはおなじみのはずです。 したがって、 オフセット式はまったく同じように機能します。 データが何らかの方法で順序付けされた後、オフセット式を適用できます。
オフセット式を使用できる状況
オフセットに関する以降のすべての例では、このスクリプトの結果として構築されたデータセットを使用します。
-- Declare @tblSample Table ( [Person Name] Varchar(50) ,Age int ,Address Varchar(100) ) -- Insert into @tblSample Select 'Person Name' + CAST(Number AS VARCHAR) , Number ,'Address' + CAST(Number AS VARCHAR) From master..spt_values Where Type = 'p' And Number Between 1 and 50
タスク1.最初の10エントリをスキップし、残りを表示します。
スクリプトは簡単です。
Select * From @tblSample Order by Age Offset 10 Row
または
Select * From @tblSample Order by Age Offset (10) Rows
出力は次のようになります。
Person Name Age Address Person Name11 11 Address11 Person Name12 12 Address12 . . . . . . . . . . . . . . . . . . . .. . . . . . . . . Person Name49 49 Address49 Person Name50 50 Address50
行数を示した後に使用する単語は関係ありません： 行または行 -この場合は同義語です。
タスク2.スキップする行数を変数として渡す
-- - Declare @RowSkip As int -- Set @RowSkip = 10 -- Select * From @tblSample Order by Age Offset @RowSkip Row
タスク3.式としてスキップする行数を設定する
-- 14 50 Select * From @tblSample Order by Age Offset (select MAX(number)/99999999 from master..spt_values) Rows
式select master（number）/ 99999999 from master..spt_valuesは、番号14を返します。
タスク4.ユーザー定義関数としてスキップする行数を設定します。
Select * From @tblSample Order by Age Offset (select dbo.fn_test()) Rows
スカラーユーザー定義関数のコード
CREATE FUNCTION fn_test() RETURNS int AS BEGIN Declare @ResultVar as int Select @ResultVar = 10 RETURN @ResultVar END GO
タスク5.内部ビュー、関数、サブクエリ、入れ子になったテーブル、共通テーブル式（共通テーブル式-CTE）での順序によるオフセットの使用。
たとえば、一般的な用語で使用します。
;With Cte As ( Select * From @tblSample Order By Age Offset 10 Rows) Select * From Cte
次の例は、ネストされたテーブル内でのオフセットと順序による使用を示しています。
Select * From (Select * From @tblSample Where Age >10 Order By Age Offset 10 Rows) As PersonDerivedTable
そして、表現を伴うオフセットと順序の作業の別の例。
-- view Create View vwPersonRecord AS Select * FROM tblSample GO -- view Select * From vwPersonRecord Where Age > 10 Order By Age Offset 10 Rows
オフセットが機能しない場合
1.これは「拡張メソッド」であるため、式による順序なしでは何も機能しません。
Select * From @tblSample Offset (10) Rows
エラーを報告する
メッセージ102、レベル15、状態1、行21 '10'付近の構文が正しくありません。
2.オフセットに負の値を設定することはできません。
Select * From @tblSample Order by Age Offset (-10) Rows
SQLサーバーエンジンが生成します
メッセージ10742、レベル15、状態1、行22 OFFSET句で指定されたオフセットは負の値にできません。
3.整数型以外の値は指定できません。
Select * From @tblSample Order by Age Offset 10.5 Rows
または
Select * From @tblSample Order by Age Offset Null Rows
くれます
メッセージ10743、レベル15、状態1、行24 OFFSET句に提供される行の数は整数でなければなりません。
4. Over（）式内では使用できません。
;With Cte As ( Select *, Rn = Row_Number() Over(Order by Age Offset 10 Rows) From @tblSample ) Select * from Cte
リクエストの実行中にメッセージを取得します
メッセージ102、レベル15、状態1、行22「オフセット」付近の構文が正しくありません。
Fetch First / Fetch Nextを使用する
これらのキーワードは、Offset式を使用して文字列の配列をスキップした後に返される行の数を指定するために使用されます。 100行あり、最初の10行をスキップして次の5行を取得する必要があるとします。 つまり 11から15の行を取得する必要があります。
Select * From <SomeTable> Order by <SomeColumn> Offset 10 Rows Fetch Next 5 Rows Only; -- Fetch First 5 Rows Only
このようなクエリは、予想される行数を返します。 .Netのプログラマーは、Take拡張メソッドをすぐに呼び出します。
次に、これらのキーワードを適用できる状況を検討します。
タスク1.最初の10エントリをスキップし、次の5エントリを取得します
Select * From @tblSample Order by Age Offset 10 Row Fetch First 5 Rows Only
結果は次のようになります。
Person Name Age Address Person Name11 11 Address11 Person Name12 12 Address12 Person Name13 13 Address13 Person Name14 14 Address14 Person Name15 15 Address15
タスク2.変数を使用して出力する行数を設定する
-- Declare @RowSkip As int -- - Declare @RowFetch As int -- - Set @RowSkip = 10 -- - Set @RowFetch = 5 -- 11 15 Select * From @tblSample Order by Age Offset @RowSkip Row Fetch Next @RowFetch Rows Only;
一般に、これらのキーワードを使用すると、オフセットと同じことができます。 サブクエリ、ビュー、関数など
最初にフェッチ/次にフェッチが機能しない場合
これらのキーワードの制限は、オフセットの制限と完全に一致しています。
SQL Server 2005/2008のオフセットおよび次のフェッチシミュレーション
SQL Serverの以前のバージョンでは、Row_Number（）ランキング関数を使用して同じ機能を取得できました。 もちろん、コードはそれほどエレガントで簡潔ではないことが判明しました、例えば：
-- Declare @RowSkip As int -- - Declare @RowFetch As int -- Set @RowSkip = 10 Set @RowFetch = 5 ;With Cte As ( Select rn=ROW_NUMBER() Over(Order by (Select 1) /* */ ) ,* From @tblSample ) -- 11 15 Select [Person Name] ,Age ,Address From Cte -- Offset Fetch First/Fetch Next Where rn Between (@RowSkip+1) -- Offset And (@RowSkip+ @RowFetch) -- Fetch First/Fetch Next Clause
CTE内では、単に行に番号を付けるサービス列が生成され、その後、このフィールドによって行がフィルタリングされます。 この方法は理解できるほど高速ではありません。
SQL Server 2000のオフセットとフェッチの次のシミュレーション
これらの古いサーバーにはランキング機能はありませんでしたが、それでも議論された機能を繰り返すことができました。 次に、自動増分フィールドを持つ一時テーブルが使用されました。 サンプルスクリプト：
Declare @RowSkip As int Declare @RowFetch As int Set @RowSkip = 10 Set @RowFetch = 5 -- , IF OBJECT_ID('tempdb..#Temp') IS NOT NULL BEGIN Drop Table #Temp END -- Create Table #Temp ( Rn int Identity ,[Person Name] Varchar(50) ,Age int ,Address Varchar(100) ) -- Insert Into #Temp([Person Name],Age,Address) Select [Person Name],Age,Address From @tblSample -- 11 15 Select [Person Name] ,Age ,Address From #Temp -- Offset Fetch First/Fetch Next Where Rn Between (@RowSkip+1) -- Offset And (@RowSkip+ @RowFetch) -- Fetch First/Fetch Next
このスクリプトでは、ターゲットテーブルのデータが上書きされる一時テーブルが最初に作成されます。 さらに、一時テーブルには自動インクリメントフィールドがあり、それによって必要な行が要求されます。
時間とリソースの測定によるオフセットとフェッチの練習
オフセットとフェッチの使用と目的に関するこれまでのすべての説明が、それらがなぜ必要なのか、どこで使用できるのかを明確に理解できたと確信しています。 既存のコードを最適化するためのアイデアが生まれました。 さらに、オフセットが有用な場合の実際のプラクティスの例を検討します。 さまざまなSQLサーバーでのパフォーマンス測定の結果も表示されます。 テストは100万行のサンプルで実行されます。
まず、次のスクリプトを使用してアカウントテーブルを作成します 。
-- tblSample, IF OBJECT_ID('tblSample','U') IS NOT NULL BEGIN DROP TABLE tblSample END GO -- Create Table tblSample ( [Person ID] Int Identity ,[Person Name] Varchar(100) ,Age Int ,DOB Datetime ,Address Varchar(100) ) GO -- Insert into tblSample Select 'Person Name' + CAST(N AS VARCHAR) , N ,DATEADD(D,N, '1900-01-01') ,'Address' + CAST(N AS VARCHAR) From dbo.tsqlc_Tally Where N Between 1 and 1000000 -- Select * From tblSample
サーバー側のデータブラウジング
ページブラウジングは、データベースのレコードを表示するシステムで最も一般的な機能です。 クライアント側とサーバー側の両方でこれを実行できるようになりました。 クライアント側でのページングとは、プログラムでページネーションを行うために、テーブル全体またはその大部分をメモリにロードすることを意味します。 一方、これはサーバー側で行うことができ、アプリケーションは目的のページを表示するために要求したデータのみを受け取ります。 このアプローチにより、データの送信、後処理、およびメモリへの保存にかかる時間が短縮されます。 つまり アプリケーションのパフォーマンスが大幅に向上します。
実験のために、最初の20,000エントリをスキップし、次の50,000エントリを取得します。
SQL Server 2000のアプローチ
-- DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE USE TSQLDB; GO SET STATISTICS IO ON; SET STATISTICS TIME ON; GO -- Declare @RowSkip As int Declare @RowFetch As int -- Set @RowSkip = 20000 Set @RowFetch = 50000 -- , IF OBJECT_ID('tempdb..#Temp') IS NOT NULL BEGIN Drop Table #Temp END -- Create Table #Temp ( Rn int Identity ,[Person ID] int ,[Person Name] Varchar(50) ,Age int ,DOB datetime ,Address Varchar(100) ) -- Insert Into #Temp([Person ID],[Person Name],Age,DOB,Address) Select [Person ID],[Person Name],Age,DOB,Address From dbo.tblSample -- 20 000 70 000 Select [Person ID] ,[Person Name] ,Age ,DOB ,Address From #Temp -- Offset Fetch First/Fetch Next Where Rn Between (@RowSkip+1) -- Offset And (@RowSkip+ @RowFetch) -- Fetch First/Fetch Next GO SET STATISTICS IO OFF; SET STATISTICS TIME OFF; GO
スクリプトを理解するには、前の例とコメントで十分だと思います。
リードタイム：
SQL Serverの実行時間：
CPU時間= 110ミリ秒、経過時間= 839ミリ秒。
I / O統計：
スキャンカウント1
論理読み取り8037、
物理読み取り0、
先読みは0を読み取り、
lob論理読み取り0、
lob物理読み取り0、
lob先読みは0を読み取ります。
SQL Server 2005/2008のアプローチ
DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE USE TSQLDB; GO SET STATISTICS IO ON; SET STATISTICS TIME ON; GO Declare @RowSkip As int Declare @RowFetch As int Set @RowSkip = 20000 Set @RowFetch = 50000 ;With Cte As ( Select rn=ROW_NUMBER() Over(Order by (Select 1)) ,* From dbo.tblSample ) Select [Person ID] ,[Person Name] ,Age ,DOB ,Address From Cte Where rn Between (@RowSkip+1) <em> </em> And (@RowSkip+ @RowFetch) GO SET STATISTICS IO OFF; SET STATISTICS TIME OFF; GO
リードタイム：
SQL Serverの実行時間：
CPU時間= 78ミリ秒、経過時間= 631ミリ秒。
I / O統計：
スキャンカウント1
論理読み取り530、
物理読み取り0、
先読みは1549を読み取り、
lob論理読み取り0、
lob物理読み取り0、
lob先読みは0を読み取ります。
SQL Server 2011のアプローチ
DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE USE TSQLDB; GO SET STATISTICS IO ON; SET STATISTICS TIME ON; GO Declare @RowSkip As int Declare @RowFetch As int Set @RowSkip = 20000 Set @RowFetch = 50000 Select * From dbo.tblSample Order by (Select 1) Offset @RowSkip Row Fetch Next @RowFetch Rows Only; GO SET STATISTICS IO OFF; SET STATISTICS TIME OFF; GO
リードタイム ：
SQL Serverの実行時間：
CPU時間= 47ミリ秒、経過時間= 626ミリ秒。
I / O統計：
スキャンカウント1
論理読み取り530、
物理読み取り0、
先読みは1549を読み取り、
lob論理読み取り0、
lob物理読み取り0、
lob先読みは0を読み取ります。
最も興味深い結果は、プロセッサー時間（CPU時間）およびランタイム（経過時間-要求の実行に必要な時間）の使用です。 測定値の比較を以下に示します。
| SQL Serverバージョン | CPU時間 | 経過時間 |
| 2000年 | 110ms | 839ミリ秒 |
| 2005/2008 | 78ms | 631ミリ秒 |
| 2011 | 46ms | 626ミリ秒 |
この表は、新しいSQL Serverが以前のバージョンよりもはるかに高速であることを明確に示しています。 当然、マシンによって時間の測定値は異なる場合がありますが、新しいサーバーのパフォーマンスは常に高くなります。
TOP式の代替
状況によっては、Denaliの新機能がTOPを置き換える場合があります。
例として、パラメータの降順でソートされた最初の10レコードを受信する必要がある場合を考えます。
以前のバージョンのアプローチ
Select Top(10) [Person ID] ,[Person Name] ,Age ,DOB ,Address From dbo.tblSample Order By Age Desc
SQL Server Denaliで可能なアプローチ
Select [Person ID] ,[Person Name] ,Age ,DOB ,Address From dbo.tblSample Order By Age Desc Offset 10 Rows
コメントに記載されているように、これは誤ったコードであり、結果をトップ（10）に戻します。
サイクルからの転送：
MS SQL Server 2011： スタンドアロンデータベース 、 新しいシーケンスオブジェクト 、 オフセットステートメント 、 エラー処理 、 結果セット構成 、 SSMSの新機能 。