Geekly Articles each Day

MS SQL 2011-新しいオフセットステートメント

新しいSQL Server 2011（Denali）は、次の2つの非常に予想される追加コマンドでOrder Byコマンドの機能を拡張します。


オフセット


このコマンドを使用すると、クエリ結果を表示する前に、指定した行数をスキップできます。 これが何を意味するのかというと、テーブルに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年110ms839ミリ秒
2005/200878ms631ミリ秒
201146ms626ミリ秒



この表は、新しい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の新機能 。

Source: https://habr.com/ru/post/J123491/

More articles:


All Articles