T-SQL: SEQUENCE を使ってシーケンス番号を振る方法

SEQUENCE を使ってシーケンス番号を振る

商品番号、発注番号、学生番号などなど、システム開発をしていると、レコードを特定する ID 番号のほかにも、シーケンス番号を使って一意となる番号を生成したい時がありますよね。

今回は SQL Server 2012 から使える SEQUENCE オブジェクトを使って、シーケンス番号を生成する方法をご紹介します。


SEQUENCE オブジェクトを生成する

SEQUENCE オブジェクトを使うと、指定したルールにしたがって数字のシーケンス番号を生成することができます。

SEQUENCE オブジェクトを生成する構文は以下の通りです。 

CREATE SEQUENCE [スキーマ名 . ] シーケンス名
    [ AS <整数のデータ型> ]  
    [ START WITH <整数> ]  
    [ INCREMENT BY <整数> ]  
    [ { MINVALUE [ <整数> ] } | { NO MINVALUE } ]  
    [ { MAXVALUE [ <整数> ] } | { NO MAXVALUE } ]  
    [ CYCLE | { NO CYCLE } ]  
    [ { CACHE [ <整数> ] } | { NO CACHE } ]  
    [ ; ]

何もオプションを指定しないと次のようなデフォルトの値が使われます。

AS -> BIGINT
START WITH -> BIGINT の最小値
INCREMENT BY -> 1 ずつ増える
NO MINVALUE -> BIGINT の最小値
NO MAXVALUE -> BIGINT の最大値
NO CYCLE -> サイクルしない
CACHE -> キャッシュする

全て必須項目はないですが、オプションを何も指定しないでシーケンスオブジェクトを生成してシーケンス番号を生成すると、最初に生成される番号が 「-9223372036854775808」 になってしまい、使いにくいです。

ですので少なくとも START WITH は指定したほうが良いと思います。

INCREMENT BY にはマイナスの値も設定可能で、その場合は最大値から最小値に向かって、降順のシーケンス番号が生成されます。

利用する時ですが、シーケンス番号はトランザクションの範囲外で生成されるので、IDENTITY と同様に、たとえトランザクションをロールバックしてもカウンターは戻りません。


それでは、実際に SEQUENCE オブジェクトを生成してみましょう。

1 から始まるシーケンスオブジェクトは次のように生成することができます。 

CREATE SEQUENCE TestSequence
    START WITH 1;

できた SEQUENCE オブジェクトは、SQL Management Studio のオブジェクトエクスプローラで、該当のデータベースの下の[プログラミング] > [シーケンス]で確認することができます。

SEQUENCE を使ってシーケンス番号を振る方法 1

シーケンス番号を生成する

生成した SEQUENCE オブジェクトからシーケンス番号を生成するには NEXT VALUE FOR を使います。

NEXT VALUE FOR はテーブルの既定の制約や、SELECT ステートメント、INSERT ステートメントなどいろいろな状況で使うことができます。


例えば TestSequence から、シーケンス番号を生成して変数に代入するクエリーは次の通りです。 

DECLARE @SeqNo1 BIGINT = NEXT VALUE FOR TestSequence;
DECLARE @SeqNo2 BIGINT = NEXT VALUE FOR TestSequence;

SELECT	@SeqNo1 AS Seq1,
	@SeqNo2 AS Seq2;

SEQUENCE を使ってシーケンス番号を振る方法 2


複数のレコードを返すクエリーに、順位付け関数と共にシーケンス番号を生成することも可能です。

例えば、 次のようなクエリーで Student テーブルのレコードを LastName、FirstName の順番でソートして、シーケンス番号を振ることができます。

SEQUENCE を使ってシーケンス番号を振る方法 3

SELECT	NEXT VALUE FOR TestSequence 
		OVER (ORDER BY LastName, FirstName) AS SeqNo,  
	*
FROM	Student;

SEQUENCE を利用して学生番号を振る

これを利用して、ST + 8 桁の数字となる学生番号を振ってみましょう。

数字の部分は 8 桁が最高にしたいので MAXVALUE が 99999999 となる StudentNoSequence 次のようなシーケンスを生成しておきます。 

CREATE SEQUENCE StudentNoSequence
    START WITH 1
    MAXVALUE 99999999;

そして、次のようなクエリーで学生番号を生成し、Student テーブルの StudentNo カラムの値を更新することができます。 

UPDATE S1
SET    S1.StudentNo = S2.StudentNo
FROM   Student AS S1
         INNER JOIN
            (SELECT 'ST' + 
                    RIGHT(
                       '00000000' + 
                        CAST(
                          NEXT VALUE FOR StudentNoSequence 
                             OVER (ORDER BY LastName, FirstName) 
                       AS NVARCHAR)
                    , 8) AS StudentNo,
                    StudentID
             FROM   Student) AS S2
         ON S1.StudentID = S2.StudentID;

SEQUENCE を使ってシーケンス番号を振る方法 4


学生番号を生成している箇所は緑で囲まれた部分です。

LastName, FirstName 順に生成したシーケンス番号を NVARCHAR 型に変換し、左側に 0 を 8 個追加して RIGHT 関数で右から 8 桁取得することによって左側をゼロパディングし、頭に 'ST' を追加しています。


この方法ですと、もし他のプロセスも同時にシーケンス番号を生成していた場合、番号が飛び飛びになる可能性があります。

どうしても番号が飛んでほしくない時は sp_sequence_get_range を使って、シーケンス番号を一度にまとめて取得することも可能です。

sp_sequence_get_range の使い方は 「SEQUENCE を使って連続したシーケンス番号を振る方法 」 をご覧ください！

© 2010-2024 SQL Server 入門