Microsoft SQL Server: シーケンスによるソリューション
新しい Microsoft SQL Server プラットフォームの特色であるシーケンスは、あらゆる SQL Server の管理者にとって非常に便利なものです。
Denny Cherry
Microsoft SQL Server では、待望の新機能 "シーケンス" が追加されました。この機能は、SQL Server の最初のパブリック ベータ版である Denali で使うことができます。SQL Server コミュニティの数多くのメンバーが何年も求めてきたシーケンス機能が、ようやく導入されました。
概念の観点では、シーケンスは、テーブルの列に配置できる IDENTITY プロパティのようなものです。IDENTITY プロパティとシーケンスの大きな違いは、シーケンスは特定のテーブルにバインドされないことです。実際、テーブルを使う必要はまったくありません。1 つのデータベースに複数のシーケンスを含めることが可能で、シーケンスの数は、データベースに含まれるオブジェクトの総数によってのみ制限されます。
シーケンスを作成する
シーケンスを作成するときには、データベースで、そのシーケンスが使用される方法や場所を指定する必要はありません。シーケンスは、データベースに含まれる他のオブジェクトから完全に独立したオブジェクトです。ただし、いくつかのオプションを指定する必要があります。
それは、シーケンスで発行できる最小値を設定する MINVALUE オプション、シーケンスが発行できる最大値を設定する MAXVALUE オプション、およびシーケンスで発行する最初の値を設定する START WITH オプションの 3 つです。START WITH オプションを省略すると、最初の値として MINVALUE オプションの値が使用されます。基本的な CREATE SEQUENCE ステートメントで MINVALUE、MAXVALUE、および START WITH オプションを指定した場合の例を次に示します。
CREATE SEQUENCE dbo.MySequence AS INT MINVALUE 1 MAXVALUE 100000 START WITH 1000
MINVALUE オプションまたは MAXVALUE オプションを指定しない場合は、これらの行を CREATE SEQUENCE ステートメントから省略するか、次のように NO MINVALUE キーワードと NO MAXVALUE キーワードを使用します。
CREATE SEQUENCE dbo.MySequence AS INT NO MINVALUE NO MAXVALUE START WITH 1000
これは、最小値と最大値を指定していない CREATE SEQUENCE ステートメントです。ただし、MINVALUE と START WITH の値はどちらも指定する必要があり、これには 2 つの理由があります。
まず、シーケンスでは、インクリメントの処理で必ずしも値を増やす必要はないというのが理由の 1 つです。大きな数値から始まって、新しい数値を追加するたびに値が小さくなるシーケンスを作成する必要もあるでしょう。2 つ目の理由は、シーケンスでは、数値がなくなったときにシーケンスの先頭に戻る可能性があることです。
範囲が 1 ～ 10,000 で、最初の値が 1,000 で、数値がなくなったら 1 から開始するシーケンスを作成するとします。数値がなくなったときに先頭に戻るシーケンスを作成するには、次のコードのように、シーケンスを作成するときに CYCLE キーワードを指定します (シーケンスが先頭に戻らないようにするには、NO CYCLE キーワードを使用します)。
CREATE SEQUENCE dbo.MySequence AS INT MINVALUE 1 MAXVALUE 100000 CYCLE
次に、NO CYCLE キーワードを使用した、同じシーケンスを示します。
CREATE SEQUENCE dbo.MySequence AS INT MINVALUE 1 MAXVALUE 100000 NO CYCLE
これらの例からわかるように、シーケンスで使用するデータ型は、AS キーワードの後に指定されます。ここで指定しているのは、INT 型 (INTEGER 型) です。その他にも、TINYINT、SMALLINT、BIGINT、または許容可能なデータ型に基づいているユーザー定義のデータ型を指定できます。
これらの整数のデータ型に加え、範囲が 0 から定義されている場合のみ、DECIMAL データ型と NUMERIC データ型を使用することもできます。データ型が指定されない場合、シーケンスでは、既定で BIGINT データ型を使用しますが、テーブルに値が格納されている INTEGER データ型の列がある場合に問題が発生することがあります。
次に指定する設定は CACHE で、これは最も重要な設定の 1 つです。CACHE 設定では、新しい値が必要になるたびに、メモリに読み込む必要がある値の数を (SQL Server がシーケンスを使用しているとき) SQL Server に通知します。シーケンスと IDENTITY プロパティの動作において大きく異なるのが、CACHE の動作です。
SQL Server では、IDENTITY プロパティを使用して、メモリ内に 20 個の値のキャッシュを保持しますが、この値を調整することはできません。IDENTITY プロパティには、テーブルに新しい行を挿入したり、IDENTITY プロパティで値を発行したりするたびに、この値が使用されたという事実が SQL Server のデータベースに書き込まれるというパフォーマンスの問題があります。行がテーブルに速く読み込まれるほど、SQL Server では、このメタデータをより迅速にデータベースに書き込む必要があります。
IDENTITY プロパティとは異なり、シーケンスでは、最初に値がキャッシュに格納されたときにのみ、値が発行されたことをデータベースのメタデータに書き込みます。たとえば、シーケンスが 1,000 行単位で行を発行した場合、最初の 1,000 行が発行されたときに、1,000 という値がメタデータに書き込まれます。1,001 という値が必要な場合、さらに 1,000 個の値がキャッシュに読み込まれ、2,000 という値がメタデータに書き込まれます。これにより、メタデータを記述する回数が大幅に減り、データベースのパフォーマンスが向上します。
欠点は、SQL Server を起動するたびに、シーケンスで発行される値にずれが生じることです。データベースでは、シャットダウンされるとき、実際に最後に使用された値をメタデータに書き込みません。このため、シャットダウン前に使用されていた最後の値が 1,005 の場合 (かつ、先ほど説明したのと同じ設定を使用している場合)、データベースがオンライン復帰後に次の行が挿入されるとき、次に使用される値は 2,000 になります。
これが原因で、シーケンスでは、IDENTITY プロパティの値よりも、かなり早く数値を切らしてしまうことになります。また、SQL Server の管理者が、値がなくなったときに混乱してしまいます。この場合は、失われた数値があることを受け入れるほかありません。
SQL Server であらゆる値を使用する必要がある場合は、キャッシュの設定を NO CACHE にします。そうすると、シーケンスの動作が IDENTITY プロパティの動作に近くなります。ただし、メタデータを追加で書き込むため、シーケンスのパフォーマンスが低下します。
省略する数値の量を制御するには、シーケンスの INCREMENT BY 設定を使用します。INCREMENT BY の設定には、あらゆる正または負の整数値を指定できますが、最も一般的な値は 1 でしょう。次のコードでは、CREATE SEQUENCE ステートメントで INCREMENT BY キーワードを使っています。
CREATE SEQUENCE dbo.MySequence AS INT MINVALUE 1 MAXVALUE 100000 INCREMENT BY 3 CYCLE
シーケンスを使用する
シーケンスの使い方は、いくつかあります。たとえば、シーケンスをテーブルの既定値として割り当てて、テーブルに行を挿入したときに列の値が自動的に指定されるようにすることができます。これにより、IDENTITY プロパティが構成されたテーブルのように動作することになります。
さらに、ストアド プロシージャ (または別の T-SQL コード) から、シーケンスの次の値を引き出すこともできます。そのため、ストアド プロシージャ (または別の T-SQL コード) の処理中にシーケンスを使用することができます。
テーブルに新しい行を追加するときに自動的に列の値として追加するシーケンスを指定するには、次のように、列の既定値を設定して、シーケンスから NEXT VALUE を引き出します。
CREATE TABLE dbo.YourTable (YourTableId INT NOT NULL, AnotherColumn VARCHAR(10)) GO ALTER TABLE dbo.YourTable ADD DEFAULT NEXT VALUE FOR dbo.MySequence FOR YourTableId GO
このようにすると、IDENTITY プロパティと同じ方法でテーブルに行が挿入されると、DEFAULT 制約の値が割り当てられます。
IDENTITY プロパティでは利用できなくて、シーケンスでは利用できる便利な処理の 1 つは、シーケンスから取得した値の前に値を追加することです。シーケンスから取得した値の前に A という文字を追加する処理は、シーケンスの出力を文字値にキャストすることで簡単に実行できます。それから、次のように、値の前に文字値を追加します。
CREATE TABLE dbo.YourTable (YourTableId INT NOT NULL, AnotherColumn VARCHAR(10)) GO ALTER TABLE dbo.YourTable ADD DEFAULT ‘A’ + CAST(NEXT VALUE FOR dbo.MySequence as VARCHAR(10)) FOR YourTableId GO
このように、IDENTITY プロパティと同じ方法でテーブルに行が挿入されるときに DEFAULT 制約の値を割り当てることが可能になりますが、この処理を実行できるのは、取得した値の最初に文字値を追加しているときだけです。
(ストアド プロシージャ内から行うか否かにかかわらず) 通常の T-SQL コードにおけるシーケンスの値の呼び出しも、同じ方法で行われます。NEXT VALUE 構文を使用して、次の値を取得する必要があるシーケンスの名前を指定します。この方法では、以下のように、シーケンスから次に使用可能な値が引き出されます。
DECLARE @YourId INT SET @YourId = NEXT VALUE FOR dbo.MySequence
また、シーケンスを使って、行セットの行に行番号を割り当てることもできます。これには、次の例のように、SELECT ステートメントで NEXT VALUE 構文を使用します。
SELECT NEXT VALUE FOR dbo.MySequence, * FROM sys.objects
このステートメントを実行すると、レコードセットの各行に、シーケンスの値が割り当てられます。
ご覧のように、この新しいシーケンス機能は、強力なツールです。SQL Server でシーケンスを使用する方法の数や多様性は、皆さんの想像力しだいで無限に広がります。
.png)
Denny Cherry は、SQL Server の管理に 10 年以上携わっています。専門は、システム アーキテクチャ、パフォーマンス チューニング、レプリケーション、トラブルシューティングです。SQL Server 2008 のマイクロソフト認定資格など、SQL Server 関連の認定資格をいくつか保有しており、数年にわたって Microsoft SQL Server MVP として活躍しています。また、SQL Server の管理や別のテクノロジと SQL Server の統合について扱った技術文書や書籍を多数執筆しています。