CREATE TABLE (Transact-SQL) IDENTITY (プロパティ)

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

テーブルに ID 列を作成します。 このプロパティは、Transact-SQL の CREATE TABLE ステートメントおよび ALTER TABLE ステートメントで使用します。

Note

IDENTITY プロパティは、列の行 ID プロパティを示す SQL-DMO Identity プロパティとは異なります。

Transact-SQL 構文表記規則

構文

IDENTITY [ (seed , increment) ]

この構文は、Azure Synapse Analytics のサーバーレス SQL プールでサポートされていません。

Note

SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。

引数

seed

テーブルに読み込まれる最初の行に使用される値です。

increment

読み込まれている前の行の ID 値に加算される増分の値です。

Note

Azure Synapse Analytics では、データ ウェアハウスの分散アーキテクチャにより、ID の値は増分になりません。 詳細については、Synapse SQL プールで IDENTITY を使用して代理キーを作成する方法に関する記事を参照してください。

seed と increment の両方を指定するか、またはどちらも指定しないでください。 どちらも指定しないときの既定値は (1,1) です。

解説

ID 列はキー値の生成に使用できます。 列の ID プロパティでは、次の条件が保証されます。

• 新しい値はそれぞれ、現在のシードと増分に基づいて生成されます。

• 特定のトランザクションの新しい各値は、テーブルの他の同時実行トランザクションとは異なります。

列の ID プロパティでは、次の条件は保証されません。

• 値の一意性: PRIMARY KEY または UNIQUE 制約、または UNIQUE インデックスを使用して、一意性を強制する必要があります。

  Note

  Azure Synapse Analytics では、PRIMARY KEY または UNIQUE 制約、または UNIQUE インデックスはサポートされません。 詳細については、Synapse SQL プールで IDENTITY を使用して代理キーを作成する方法に関する記事を参照してください。

• トランザクション内の連続する値: 複数行を挿入するトランザクションは、テーブルで同時に他の挿入が実行される可能性があるため、その複数行の連続する値を取得するとは限りません。 連続した値にする必要がある場合、トランザクションはテーブル上で排他ロックを使用するか、SERIALIZABLE 分離レベルを使用する必要があります。

• サーバーの再起動または他のエラーが発生した後の連続した値 - SQL Server では、パフォーマンス上の理由から ID 値をキャッシュすることがあります。割り当てられた値の一部は、データベースの障害やサーバーの再起動が発生したときに失われることがあります。 その結果、挿入時に非連続的な ID 値が生成される場合があります。 非連続的な値が許可されない場合、アプリケーションは独自のメカニズムを使用してキー値を生成する必要があります。 シーケンス ジェネレーターを NOCACHE オプションを指定して使用すると、非連続的な値を絶対にコミットされないトランザクションに制限することができます。

• 値の再利用: 特定のシードと増分値が指定された特定の ID プロパティでは、ID 値がエンジンによって再利用されることはありません。 特定の挿入ステートメントが失敗した場合または挿入ステートメントがロールバックされた場合、使用した ID 値は失われ、再度生成されません。 その結果、それ以降の ID 値が生成されると、連続しない場合があります。

これらの制限事項が設計に含まれているのは、パフォーマンスを向上するため、および多くの一般的な状況で許容されるためです。 これらの制限事項が原因で ID 値を使用できない場合は、アプリケーションを使用して、現在の値を保持する別のテーブルを作成し、テーブルと番号の割り当てへのアクセスを管理します。

ID 列を持つテーブルがレプリケーション用に発行されている場合、使用されているレプリケーションの種類に適した方法で、ID 列を管理する必要があります。 詳細については、「Replicate Identity Columns」 (ID 列のレプリケート) を参照してください。

ID 列は 1 つのテーブルにつき 1 つだけ作成できます。

メモリ最適化テーブルで、シードと増分を 1, 1 に設定する必要があります。 シードまたは増分を 1 以外に設定すると、次のエラーが発生します: The use of seed and increment values other than 1 is not supported with memory optimized tables

列に ID プロパティを設定すると、そのプロパティを削除することはできません。 新しいデータ型が ID プロパティと互換性がある限り、データ型を変更できます。

例

A. CREATE TABLE で IDENTITY プロパティを使用する

次の例では、ID 番号を自動的に増分するテーブルを、IDENTITY プロパティを使用して新規作成します。

USE AdventureWorks2022;
GO
IF OBJECT_ID('dbo.new_employees', 'U') IS NOT NULL
    DROP TABLE new_employees;
GO

CREATE TABLE new_employees (
    id_num INT IDENTITY(1, 1),
    fname VARCHAR(20),
    minit CHAR(1),
    lname VARCHAR(30)
);

INSERT new_employees (fname, minit, lname)
VALUES ('Karin', 'F', 'Josephs');

INSERT new_employees (fname, minit, lname)
VALUES ('Pirkko', 'O', 'Koskitalo');

B. ID 値のギャップを検索する汎用構文を使用する

次の例では、データが削除された場合に ID 値のギャップを検索する汎用構文を示します。

Note

次に示す Transact-SQL スクリプトの最初の部分は、あくまでも参考です。 実行できるのは、-- Create the img table というコメントで始まる Transact-SQL スクリプトです。

-- Here is the generic syntax for finding identity value gaps in data.
-- The illustrative example starts here.
SET IDENTITY_INSERT tablename ON;

DECLARE @minidentval column_type;
DECLARE @maxidentval column_type;
DECLARE @nextidentval column_type;

SELECT @minidentval = MIN($IDENTITY),
    @maxidentval = MAX($IDENTITY)
FROM tablename

IF @minidentval = IDENT_SEED('tablename')
    SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('tablename')
    FROM tablename t1
    WHERE $IDENTITY BETWEEN IDENT_SEED('tablename')
            AND @maxidentval
        AND NOT EXISTS (
            SELECT *
            FROM tablename t2
            WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('tablename')
            )
ELSE
    SELECT @nextidentval = IDENT_SEED('tablename');

SET IDENTITY_INSERT tablename OFF;

-- Here is an example to find gaps in the actual data.
-- The table is called img and has two columns: the first column
-- called id_num, which is an increasing identification number, and the
-- second column called company_name.
-- This is the end of the illustration example.
-- Create the img table.
-- If the img table already exists, drop it.
-- Create the img table.
IF OBJECT_ID('dbo.img', 'U') IS NOT NULL
    DROP TABLE img;
GO

CREATE TABLE img (
    id_num INT IDENTITY(1, 1),
    company_name SYSNAME
);

INSERT img (company_name)
VALUES ('New Moon Books');

INSERT img (company_name)
VALUES ('Lucerne Publishing');

-- SET IDENTITY_INSERT ON and use in img table.
SET IDENTITY_INSERT img ON;

DECLARE @minidentval SMALLINT;
DECLARE @nextidentval SMALLINT;

SELECT @minidentval = MIN($IDENTITY)
FROM img

IF @minidentval = IDENT_SEED('img')
    SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('img')
    FROM img t1
    WHERE $IDENTITY BETWEEN IDENT_SEED('img')
            AND 32766
        AND NOT EXISTS (
            SELECT *
            FROM img t2
            WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('img')
            )
ELSE
    SELECT @nextidentval = IDENT_SEED('img');

SET IDENTITY_INSERT img OFF;

関連するコンテンツ

• ALTER TABLE (Transact-SQL)
• CREATE TABLE (Transact-SQL)
• DBCC CHECKIDENT (Transact-SQL)
• IDENT_INCR (Transact-SQL)
• @@IDENTITY (Transact-SQL)
• IDENTITY (関数) (Transact-SQL)
• IDENT_SEED (Transact-SQL)
• SELECT (Transact-SQL)
• SET IDENTITY_INSERT (Transact-SQL)
• ID 列のレプリケート