SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server 2012 のシーケンスオブジェクトについて

one comment

SQL Server 2012 ではシーケンスオブジェクトが追加されています。

このシーケンスオブジェクトについて軽くまとめてみたいと思います。

■シーケンスオブジェクトの利用シナリオ


BOL には以下のように記載されています。

シーケンスは、次のシナリオで ID 列の代わりに使用します。

  • テーブルへの挿入を行う前に、アプリケーションが数値を必要とする。

  • アプリケーションが、複数のテーブル間またはテーブル内の複数の列間で、単一の番号シリーズを共有する必要がある。

  • 指定した番号に達したときに、アプリケーションが番号シリーズを再開する必要がある。たとえば、1 ~ 10 の値を割り当てた後、アプリケーションは再び 1 ~ 10 の値を割り当てます。

  • アプリケーションが、シーケンス値を別のフィールドで並べ替える必要がある。NEXT VALUE FOR 関数は、OVER 句を関数呼び出しに適用できます。OVER 句によって、返される値は OVER 句の ORDER BY 句の順で生成されることが保証されます。

  • アプリケーションが、同時に複数の番号を割り当てる必要がある。たとえば、アプリケーションで 5 つの連続する番号を予約する必要がある場合などです。ID 値を要求したときに他のプロセスが番号を同時に発行していた場合、非連続的な ID 値が生成される場合があります。sp_sequence_get_range を呼び出すことにより、シーケンス内の番号を一度に取得できます。

  • 増分値など、シーケンスの仕様を変更する必要がある。

今まで、連番を設定するときは IDENTITY を使用して設定することがあったかと思います。
IDENTITY はテーブル単位での設定となりますのでテーブル間で連続した値を共有するということができませんでした。

SQL Server 2012 で導入されたシーケンスオブジェクトはテーブルとは独立したオブジェクトとして作成されますのでテーブル間で一連の連番を共有することができます。

また、インクリメントする際の増分値や最大値まで達した時に最小値に戻って再度番号を振るといった循環の設定も可能となっています。

シーケンスオブジェクトは NEXT VALUE FOR 関数を呼び出したタイミングで連番となる値を取得できますので、テーブルに連番を挿入する前に値の加工を行うこともできます。

 

■シーケンスオブジェクトの作成方法


シーケンスオブジェクトは CREATE SEQUENCE または SSMS の GUI から作成することができます。

クエリと GUI を比較してみましたが、GUI から全設定が可能なようですね。
image

データ型としては、tinyint / smallint / int / bigint / decimal / numeric といった数字型を使用することができます。

 

■シーケンスオブジェクトでは必ず連番が設定できるか


結論から書いてしまうと必ず連番を設定するのは難しそうです。

シーケンスオブジェクトのデフォルトの設定ではキャッシュオプションが [既定のサイズ] となっています。
シーケンスオブジェクトの情報はシステムテーブルに格納されているのですが、NEXT VALUE FOR で値を取得するたびに毎回システムテーブルに情報を問い合わせしているとディスク I/O のオーバーヘッドが発生してしまいます。

そのため、NEXT VALUE FOR を実行した際にある程度の範囲でシーケンスオブジェクトの連番を取得してメモリ上にキャッシュするという動作をすることができます。
これがキャッシュをする場合の動作になります。

以下のようなシーケンスオブジェクトを作成しています。
image

クエリを実行すると 1, 2 という連番が取得できます。
image

NEXT VALUE FOR を実行した後に現在の値を確認してみると、値が更新されていることが確認できます。
image

シーケンスオブジェクトの内容は、[sys.sequences] からも確認することができます。
image

この状態で SQL Server のサービスを再起動して、再度現在の値を確認してみます。
image

そうすると現在の値が先程とは異なっていることが確認できます。
これがキャッシュをした際の動作となります。
キャッシュをした場合、ある程度の範囲のシーケンスを先行してメモリ上に取得しています。
プロパティやシステムテーブルで確認をした際には現在の値が表示されていますが、実際にはこれより先行して値が取得されています。

SQL Server のサービスを停止、SQL Server のプロセスがダウンしてしまった場合は、先行して取得されていた値が最終の設定値となりますので、キャッシュをした場合にサービスの停止を行うと連番が飛んでしまうという現象が発生します。

それではキャッシュをしない場合はどうなるでしょう。
キャッシュをしない場合は、シーケンスオブジェクトを使用するたびにシステムテーブルへの問い合わせが発生します。
そのため、シーケンスオブジェクトを使用する度にシステムテーブル (sys.sysobjvalues.clst) への更新が行われることになります。
キャッシュをした場合、キャッシュの範囲を使われている状態ではシステムテーブルへの更新は発生しません。そのため、キャッシュをしない場合と比較するとディスクの I/O が少し上がることになりますね。

キャッシュをしない場合は必ず連番を使用していくことができるかというと、トランザクション内でロールバックをした場合は連番にはならなさそうです。
# ロールバックをしてもシステムテーブルに対してのロールバック処理が行われていないようでした
image

挿入されたレコードに対して完全に連番を保証しないといけない場合にはシーケンスオブジェクトの利用は難しいのかもしれないですね。
# 歯抜けしてしまっていいのであれば問題なく使用できると思います。

シーケンスオブジェクトから連番を取得する際にはキャッシュをした場合は [SCH_S] が、キャッシュをした場合は [SCH_S] [S] [U] がシーケンスオブジェクトに対して取得されているようです。シーケンスオブジェクトの内容を更新する場合には、更新ロックが取得されているようですね。
ロックの互換性は ロックの互換性 に記載されていますが、キャッシュされている範囲を使用している場合にはロックの競合はなさそうですが、シーケンスオブジェクトの更新を行う場合には更新ロックが発生するので、このタイミングでブロッキングが発生する可能性がありそうですね。
# キャッシュしなかった場合は頻繁に瞬間的なブロッキングが発生していそうです。

システム開発からは遠ざかっているので連番の歯抜けが許容されないケースがどれだけあるかが認識できていないのですが、トランザクション系のテーブルで完全な連番でデータを追加しなくてはいけない場合はこの辺の挙動を把握しておいた方が良さそうですね。

Written by masayuki.ozawa

5月 24th, 2012 at 10:47 pm

Posted in SQL Server

Tagged with ,

One Response to 'SQL Server 2012 のシーケンスオブジェクトについて'

Subscribe to comments with RSS or TrackBack to 'SQL Server 2012 のシーケンスオブジェクトについて'.

  1. SQL Server 2012 のシーケンスオブジェクトについて « SE の雑記…

    素敵なエントリーの登録ありがとうございます – .NET Clipsからのトラックバック…

    .NET Clips

    25 5月 12 at 10:48

Leave a Reply

*