SE の雑記

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 から全設定が可能なようですね。

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

 

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

---

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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