Geekly Articles each Day

MS SQL 2011-新しいシーケンスオブジェクト

Oracle、DB2、PostgreSQL、および他の多くのリレーショナルデータベースのユーザーが今日驚くことのない機会がMS SQL Serverに登場しました。 シーケンスの舞台で！

シーケンス-IDと同じ方法で数字のシーケンスを生成します。 ただし、シーケンスの主な利点は、シーケンスが特定のテーブルに依存せず、データベースオブジェクトであることです。

SQL Server 2008で記述されたサンプルスクリプトを考えてみましょう。2つの列を持つ単純なテーブルを作成します。その1つは自動インクリメントになります。

Create Table WithOutSequence1 ( EmpId int identity not null primary key ,EmpName varchar(50) not null ) Insert into WithOutSequence1 Select 'Violet' Union All Select 'Tape' Select * from WithOutSequence1

同様に、別のテーブルを作成します。
 Create Table WithOutSequence2 ( EmpId int identity not null primary key ,EmpName varchar(50) not null ) Insert into WithOutSequence2 Select 'Violet' Union All Select 'Tape' Select * from WithOutSequence2

例からわかるように、テーブルに値を記録し、インクリメンタルフィールドの値を自動的に独立して入力しました。 このフィールドの値を別のテーブルで再利用することはできません。 Sequenceを使用してこの状況から抜け出す方法を見てみましょう。

コマンドの一般的な構文は次のとおりです。
 CREATE SEQUENCE [schema_name . ] sequence_name [ AS { built_in_integer_type | user-defined_integer_type } ] | START WITH <constant> | INCREMENT BY <constant> | { MINVALUE <constant> | NO MINVALUE } | { MAXVALUE <constant> | NO MAXVALUE } | { CYCLE | NO CYCLE } | { CACHE [<constant> ] | NO CACHE }




一連の数字を作成します。
 IF EXISTS (SELECT * FROM sys.sequences WHERE NAME = N'GenerateNumberSequence' AND TYPE='SO') DROP Sequence GenerateNumberSequence GO SET ANSI_NULLS ON GO CREATE SEQUENCE GenerateNumberSequence START WITH 1 INCREMENT BY 1; GO

指定されたスクリプトを実行した後、データベースオブジェクトのブラウザの[シーケンス]ノードで、オブジェクトを見つけることができます。



オブジェクトが作成されたら、以下に示すように、テーブルの作成と入力にそれを使用できます。
 Create Table WithSequence1 ( EmpId int not null primary key ,EmpName varchar(50) not null ); Insert into WithSequence1(EmpId, EmpName) VALUES (NEXT VALUE FOR GenerateNumberSequence, 'Violet'), (NEXT VALUE FOR GenerateNumberSequence, 'Tape') SELECT * FROM WithSequence1;

同じ方法で2番目のテーブルを作成する場合、 GenerateNumberSequenceを再び使用して、オブジェクトのエンドツーエンドの番号付けを取得できます。
 Create Table WithSequence2 ( EmpId int not null primary key ,EmpName varchar(50) not null ); Insert into WithSequence2(EmpId, EmpName) VALUES (NEXT VALUE FOR GenerateNumberSequence, 'Violet'), (NEXT VALUE FOR GenerateNumberSequence, 'Tape') SELECT * FROM WithSequence2;

作成したシーケンス（Sequence）はsysシステムディレクトリにあります。 シーケンス 。
 SELECT Name ,Object_ID ,Type ,Type_Desc ,Start_Value ,Increment ,Minimum_Value ,Maximum_Value ,Current_Value ,Is_Exhausted FROM sys.sequences



これはシーケンスで利用可能なすべての情報ではなく、これらの列が後で必要になります。 すべての情報を取得するには、列名をアスタリスクに置き換えます。 Is_Exhaustedについては後述します。

シーケンスには次のタイプがあります。

シーケンスを1つから開始する必要はありません。 宣言された型の可能な値内の任意の数で開始できます。 たとえば、整数値の場合、これは-2147483648〜2147483647です。

シードを範囲外に設定するときにSQL Serverが何を言うかを実際に確認してみましょう。 左のボーダーから始めましょう。
 CREATE SEQUENCE GenerateNumberSequence START WITH -2147483649 --outside the range of the int datatype boundary INCREMENT BY 1;

指定されたデータ型の引数「START WITH」に無効な値が指定されました。

予想通り。 では、右の境界線を壊しましょう。
 CREATE SEQUENCE GenerateNumberSequence START WITH 2147483647 --the max range of the int datatype INCREMENT BY 1;


サーバーは次のようなエラーを通知します。

シーケンスオブジェクト 'GenerateNumberSequence'キャッシュサイズは、使用可能な値の数よりも大きくなっています。 キャッシュサイズは、残りのシーケンス値に合わせて自動的に設定されています。

また、sys.sequencesディレクトリのIs_Exhausted列に注目すると、値が1になっていることがわかります。これにより、このシーケンスをさらに使用することは不可能であることがわかります。



このシーケンスを使用してテーブルを作成しようとすると、サーバーはエラーをスローします。

シーケンスオブジェクト 'GenerateNumberSequence'が最小値または最大値に達しました。 シーケンスオブジェクトを再起動して、新しい値を生成できるようにします。

これは、指定されたシーケンスを再起動するエンジンの要求として解釈できます。 これを行うには、 RESTART WITH構文を使用します。
 ALTER SEQUENCE dbo.GenerateNumberSequence RESTART WITH 1;

値は、宣言された型の有効な範囲内にある必要があります。 次に、シーケンスは次ではなく、指定された値で始まります。

つまり 設定されている場合：
 ALTER SEQUENCE dbo.GenerateNumberSequence RESTART WITH 10;


次に、スクリプトを実行します。
 Insert into WithSequence1(EmpId, EmpName) VALUES (NEXT VALUE FOR GenerateNumberSequence, 'violet'), (NEXT VALUE FOR GenerateNumberSequence, 'tape') SELECT * FROM WithSequence1;

結果は次のようになります。
 EmpId   EmpName -----   ------- 10      violet 11      tape

シーケンスはセットポイントから開始しました。

sysディレクトリから最小値と最大値を取得できます。 シーケンス 。

MINおよびMAX値


シーケンスの場合、許容値の境界を指定できます。 そのようなスクリプトを以下で実行してみましょう。
 CREATE SEQUENCE GenerateNumberSequence START WITH 1 INCREMENT BY 1 MINVALUE 10 MAXVALUE 20

最小値は10、最大値は20ですが、初期値を1に設定しようとしています。 これは許容範囲外であるため、次のメッセージに満足します。

シーケンスオブジェクト 'GenerateNumberSequence'の開始値は、シーケンスオブジェクトの最小値と最大値の間になければなりません。

さらに、シーケンスの次の値が境界に違反していることを想像できます。 この場合、エラーが発生します：

シーケンスオブジェクト 'GenerateNumberSequence'が最小値または最大値に達しました。 シーケンスオブジェクトを再起動して、新しい値を生成できるようにします。

問題を解決する方法は2つあります。

CYCLEオプション


このオプションはシーケンスをループし、最大値に達すると、シーケンスは最小から継続します。 例：
 CREATE SEQUENCE GenerateNumberSequence START WITH 20 INCREMENT BY 1 MINVALUE 10 MAXVALUE 20 CYCLE


最大値に達すると、結果は次のようになります。
 EmpId   EmpName -----   ------- 10      Tape 20      Violet

選択には、リクエストが使用されました。
 Insert into WithSequence1(EmpId, EmpName) VALUES (NEXT VALUE FOR GenerateNumberSequence, 'Violet'), (NEXT VALUE FOR GenerateNumberSequence, 'Tape') SELECT * FROM WithSequence1;

出力を注意深く見ると、エントリが混同されていることがわかります。 シーケンスを使用しなかった場合、結論は次のようになります。
 EmpId   EmpName -----   ------- 20      Violet 21      Tape

しかし、2番目のレコードが有効な値の範囲を超えたという事実により、数値はシーケンスに設定された最小値にリセットされました（10）。 ここでsys.sequencesディレクトリを見ると、現在の値が10であることがわかります。

次回、テーブルにデータを追加するには次のようにします。
 EmpId   EmpName ----    ------- 11      Violet 12      Tape

この時点で、Sequenceはレコードが挿入される順序を確認します。「Violet」は「Tape」の前にあり、現在の番号は10なので、レコードは次のように挿入されます。

Next_value = Current_value + Shift i.e. 「バイオレット」に10 +1が割り当てられます。 これで、シーケンスの値は11になり、2番目のレコードの値は同じ式に従って12になります。

NO CYCLEオプション


このオプションの動作は、最初からすでに考慮されており、シーケンス作成時のデフォルトです。

Over（）と組み合わせたシーケンス


以下に示すように、シーケンスをOver式とともに使用して、シーケンス番号を生成できます。
 --Declare a table Declare @tblEmp Table ( EmpId int identity ,EmpName varchar(50) not null ) --Populate some records Insert Into @tblEmp Select 'Niladri' Union All Select 'Arina' Union All Select 'Deepak' Union All Select 'Debasis' Union All Select 'Sachin' Union All Select 'Gaurav' Union All Select 'Rahul' Union All Select 'Jacob' Union All Select 'Williams' Union All Select 'Henry' --Fire a query SELECT e.* ,Seq = NEXT VALUE FOR GenerateNumberSequence OVER (ORDER BY EmpName) FROM @tblEmp e

結果：



レコードがソートされ、保存されたデータにシーケンスが正しく適用されたことがわかります。 これは、レコードが最初にソートされ、その後にのみシーケンス番号が適用されることを意味します。

関数にNext Valueを使用する際の制限。


どのような場合でも、Sequenceは次のものと組み合わせて使用​​しないでください。

sp_sequence_get_range関数


NEXT VALUE FORを使用してテーブルに行を追加するために上記で使用したすべてのアプローチを検討すると、この式がすべてのVALUESレベルに存在することがわかります。 代わりに、sp_sequence_get_range関数を使用して、後で使用できる値の望ましい範囲を取得できます。 次に、これを行う方法を示します。
 --  ,    IF EXISTS (SELECT * FROM sys.sequences WHERE NAME = N'GenerateRangeNumberSequence' AND TYPE='SO') DROP Sequence GenerateRangeNumberSequence GO --  ,    IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_RangeSequence' AND type = 'U') DROP TABLE tbl_RangeSequence GO SET ANSI_NULLS ON GO --   CREATE SEQUENCE GenerateRangeNumberSequence START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2000 CYCLE GO --   CREATE TABLE [dbo].[tbl_RangeSequence]( [EmpId] [int] NOT NULL, [EmpName] [varchar](50) NOT NULL, PRIMARY KEY CLUSTERED ( [EmpId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO --     sp_sequence_get_range DECLARE @sequence_name nvarchar(100) = N'GenerateRangeNumberSequence', @range_size int = 1000, @range_first_value sql_variant, @range_last_value sql_variant, @sequence_increment sql_variant, @sequence_min_value sql_variant, @sequence_max_value sql_variant; --   sp_sequence_get_range EXEC sp_sequence_get_range @sequence_name = @sequence_name, @range_size = @range_size, @range_first_value = @range_first_value OUTPUT, @range_last_value = @range_last_value OUTPUT, @sequence_increment = @sequence_increment OUTPUT, @sequence_min_value = @sequence_min_value OUTPUT, @sequence_max_value = @sequence_max_value OUTPUT; --   SELECT @range_size AS [Range Size], @range_first_value AS [Start Value], @range_last_value AS [End Value], @sequence_increment AS [Increment], @sequence_min_value AS [Minimum Value], @sequence_max_value AS [Maximum Value]; --       ;With Cte As ( Select Rn = 1, SeqValue = Cast(@range_first_value as int) Union All Select Rn+1, Cast(SeqValue as int) + Cast( @sequence_increment as int) From Cte Where Rn<@range_last_value ) --  100  Insert into tbl_RangeSequence(EmpId, EmpName) Select SeqValue,'Name' + Cast(SeqValue as varchar(3)) From Cte Where SeqValue<=100 Option (MaxRecursion 0) --   SELECT * FROM tbl_RangeSequence

実行結果は次のとおりです。



ここで、シーケンスが1000に増加し、不明な値がどこでも使用されていないことがわかります。 この場合、値を挿入するためにそれらを使用しました。

シーケンスとアイデンティティの比較


次の要因により、それらの間にグローバルな等号を置かないでください。

そして、シーケンスについてもう少し説明します。

MSDNでシーケンスの詳細を読むことができます。
  1. シーケンスを作成
  2. シーケンス番号の作成と使用
  3. sp_sequence_get_range



サイクルからの転送：
MS SQL Server 2011： スタンドアロンデータベース 、 新しいシーケンスオブジェクト 、 オフセットステートメント 、 エラー処理 、 結果セット構成 、 SSMSの新機能 。

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

More articles:


All Articles