自動値を生成する

  • 3 分

特定のテーブル内の 1 つの列に対して連続する値を自動的に生成することが必要になる場合があります。 Transact-SQL には、テーブル内の特定の列で IDENTITY プロパティを使用する方法と、SEQUENCE オブジェクトを定義してそのオブジェクトによって生成された値を使用するという 2 つの方法があります。

IDENTITY プロパティ

IDENTITY プロパティを使用するには、小数点以下の桁数が 0 (整数のみ) の数値データ型を使用して列を定義し、IDENTITY キーワードを含めます。 許容される型は、すべての整数型と、小数点以下の桁数に明示的に 0 を指定した 10 進数型です。

必要に応じて、シード (開始値) と増分 (ステップ値) を指定することもできます。 シードと増分を指定しないと、両方とも 1 に設定されます。

注意

IDENTITY プロパティは、列定義において NULL または NOT NULL を指定する代わりに指定されます。 IDENTITY プロパティを指定した列は、自動的に NULL 値を許容されなくなります。 文書化のためだけに NOT NULL を指定することはできますが、列を NULL (NULL 許容) として指定すると、テーブル作成ステートメントでエラーが発生します。

IDENTITY プロパティを設定できるのは、テーブル内の 1 つの列のみです。これは、PRIMARY KEY または代替キーとしてよく使用されます。

次のコードは、前のセクションの例で使用した Sales.Promotion テーブルを作成するものですが、ここでは主キーとして PromotionID という名前の IDENTITY 列を使用しています。

CREATE TABLE Sales.Promotion
(
PromotionID int IDENTITY PRIMARY KEY,
PromotionName varchar(20),
StartDate datetime NOT NULL DEFAULT GETDATE(),
ProductModelID int NOT NULL REFERENCES Production.ProductModel(ProductModelID),
Discount decimal(4,2) NOT NULL,
Notes nvarchar(max) NULL
);

注意

CREATE TABLE ステートメントの詳細については、このモジュールでは説明しません。

ID 列へのデータの挿入

列に対して IDENTITY プロパティが定義されている場合は、通常、テーブルへの INSERT ステートメントでは IDENTITY 列の値を指定しません。 データベース エンジンにより、その列に使用できる次の値を使用して値が生成されます。

たとえば、PromotionID 列の値を指定せずに、Sales.Promotion テーブルに行を挿入できます。

INSERT INTO Sales.Promotion
VALUES
('Clearance Sale', '01/01/2021', 23, 0.10, '10% discount')

VALUES 句に PromotionID 列の値が含まれていませんが、INSERT 句で列リストを指定する必要はないことに注意してください。ID 列は、この要件から除外されます。

この行がテーブルに最初に挿入される行の場合、結果は次のような新しい行になります。

PromotionID

PromotionName

StartDate

ProductModelID

Discount

Notes

1

Clearance Sale

2021-01-01T00:00:00

23

0.1

10% discount

テーブルが作成されたとき、IDENTITY 列にシード値または増分値が設定されなかったので、最初の行は値 1 で挿入されます。 次に挿入される行の PromotionID には、値 2 が割り当てられます。以下同様です。

ID 値の取得

同じセッションおよびスコープ内で最近割り当てられた IDENTITY 値を取得するには、次のように SCOPE_IDENTITY 関数を使用します。

SELECT SCOPE_IDENTITY();

この SCOPE_IDENTITY 関数からは、任意のテーブルに対する現在のスコープで生成された最新の ID 値が返されます。 特定のテーブルの最新の ID 値が必要な場合は、次のように IDENT_CURRENT 関数を使用できます。

SELECT IDENT_CURRENT('Sales.Promotion');

ID 値のオーバーライド

自動的に生成された値をオーバーライドして、IDENTITY 列に特定の値を割り当てる必要がある場合は、最初に、SET IDENTITY INSERT table_name ON ステートメントを使用して、ID の挿入を有効にする必要があります。 このオプションを有効にすると、他の列と同じように、ID 列に明示的な値を挿入できます。 終わったら、SET IDENTITY INSERT table_name OFF ステートメントを使用することで、明示的に入力した最後の値をシードとして使用して、自動 ID 値の使用を再開することができます。

SET IDENTITY_INSERT SalesLT.Promotion ON;

INSERT INTO SalesLT.Promotion (PromotionID, PromotionName, ProductModelID, Discount)
VALUES
(20, 'Another short sale',37, 0.3);

SET IDENTITY_INSERT SalesLT.Promotion OFF;

学習したように、IDENTITY プロパティは、テーブル内の列の値のシーケンスを生成するために使用されます。 ただし、IDENTITY プロパティは、データベース内の複数のテーブル間で値を調整する場合には適していません。 たとえば、直接販売とりせらーへの販売を区別し、それらの売上データを別々のテーブルに格納するとします。 どちらの種類の販売でも一意の請求書番号が必要で、2 つの異なる種類の販売で同じ値が重複しないようにすることが必要になる場合があります。 この要件の解決策の 1 つは、両方のテーブルを対象として一意の連続する値のプールを維持することです。

ID 列の再シード

場合によっては、列の ID 値をリセットまたはスキップする必要があります。 これを行うには、DBCC CHECKIDENT 関数を使用して列を "再シード" します。 これを使用すると、多くの値をスキップしたり、テーブル内のすべての行を削除した後に次の ID 値を 1 にリセットしたりできます。 DBCC CHECKIDENT の使用の詳細については、「Transact-SQL リファレンス ドキュメント」を参照してください。

SEQUENCE

Transact-SQL では、シーケンス オブジェクトを使用することで、特定のテーブルとは独立して新しい連続値を定義できます。 シーケンス オブジェクトは、CREATE SEQUENCE ステートメントを使用して作成します。必要に応じて、データ型 (整数型、または小数点以下の桁数が 0 の 10 進数または数値にする必要があります)、開始値、増分値、最大値、およびパフォーマンスに関連するその他のオプションを指定します。

CREATE SEQUENCE Sales.InvoiceNumber AS INT
START WITH 1000 INCREMENT BY 1;

シーケンスから次に使用可能な値を取得するには、次のように NEXT VALUE FOR コンストラクトを使用します。

INSERT INTO Sales.ResellerInvoice
VALUES
(NEXT VALUE FOR Sales.InvoiceNumber, 2, GETDATE(), 'PO12345', 107.99);

IDENTITY または SEQUENCE

自動入力値に IDENTITY 列または SEQUENCE オブジェクトのどちらを使用するかを決定するときは、次の点に注意してください。

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

  • SEQUENCE を使用すると、別の列で値を並べ替えることができます。 NEXT VALUE FOR コンストラクトで OVER 句を使用して、並べ替え列を指定できます。 OVER 句によって、返される値は OVER 句の ORDER BY 句の順で生成されることが保証されます。 また、この機能を使用すると、SELECT で返される行の行番号を生成できます。 次の例では、Production.Product テーブルは Name 列で並べ替えられており、最初に返される列がシーケンシャル番号です。

    SELECT NEXT VALUE FOR dbo.Sequence OVER (ORDER BY Name) AS NextID,
    ProductID,
    Name
FROM Production.Product;

    前のステートメントでは表示するためだけに SEQUENCE 値を選択していましたが、それでも値は "使用済み" になり、表示された SEQUENCE 値は使用できなくなります。 上記の SELECT を複数回実行すると、その度に異なる SEQUENCE 値が取得されます。

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

  • SEQUENCE を使用すると、増分値など、シーケンスの指定を変更できます。

  • IDENTITY 値は更新から保護されています。 IDENTITY プロパティが設定されている列を更新しようとすると、エラーが発生します。