専用 SQL プールで IDENTITY を使用して代理キーを作成する

[アーティクル]
02/08/2025

この記事では、IDENTITY プロパティを使用して専用 SQL プール内のテーブルに代理キーを作成する場合の推奨事項と例を紹介します。

代理キーとは

テーブルの代理キーは、各行の一意の識別子を持つ列です。このキーはテーブルデータからは生成されません。データモデラーは、データウェアハウスモデルを設計するときに、テーブルに代理キーを作成するのを好みます。 IDENTITY プロパティを使用すると、この目的を簡単かつ効果的に達成でき、読み込みのパフォーマンスが影響を受けることもありません。

Note

Azure Synapse Analytics:

IDENTITY 値は各ディストリビューションで自動的に増加し、他のディストリビューションの IDENTITY 値と重複しません。 Synapse の IDENTITY 値は、ユーザーが SET IDENTITY_INSERT ON を使用して重複する値を明示的に挿入するか IDENTITY を再シードする場合は、一意であるとは限りません。詳細については、「CREATE TABLE (Transact-SQL) IDENTITY (プロパティ)」を参照してください。
ディストリビューション列の UPDATE では、IDENTITY 値が一意であることは保証されません。ディストリビューション列の UPDATE 後に DBCC CHECKIDENT (Transact-SQL) を使用して、一意性を確認します。

IDENTITY 列があるテーブルを作成する

IDENTITY プロパティは、読み込みパフォーマンスに影響を与えずに、専用 SQL プール内のすべてのディストリビューションにスケールアウトするように設計されています。そのため、IDENTITY の実装ではこれらの目標を達成することを目指します。

次のステートメントのような構文を使用して、テーブルを最初に作成するときに、IDENTITY プロパティを持つようにテーブルを定義できます。

CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1) NOT NULL,
     C2 INT NULL
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

その後、INSERT..SELECT を使ってテーブルを設定します。

以降、このセクションでは、理解を深めるのに役立つ実装の詳細に注目します。

値の割り当て

IDENTITY プロパティでは、データウェアハウスの分散アーキテクチャのため、代理値が割り当てられる順序は保証されません。 IDENTITY プロパティは、読み込みパフォーマンスに影響を与えずに、専用 SQL プール内のすべてのディストリビューションにスケールアウトするように設計されています。

次にその例を示します。

CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1) NOT NULL,
     C2 VARCHAR(30) NULL
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

INSERT INTO dbo.T1
VALUES (NULL);

INSERT INTO dbo.T1
VALUES (NULL);

SELECT *
FROM dbo.T1;

DBCC PDW_SHOWSPACEUSED('dbo.T1');

この例では、2 つの行はディストリビューション 1 に格納されます。 1 番目の行の代理値は C1 列の 1 であり、2 番目の行の代理値は 61 です。これらの値はどちらも IDENTITY プロパティによって生成されたものです。ただし、値の割り当ては隣接していません。この動作は仕様です。

非対称のデータ

データ型の値の範囲は、ディストリビューション全体に均等に分散されます。分散テーブルが非対称データによって悪影響を受ける場合、データ型に対して使用可能な値の範囲が早く不足する可能性があります。たとえば、すべてのデータが最終的に 1 つのディストリビューションに格納される場合、実質的にテーブルはそのデータ型の値の 60 分の 1 にのみアクセスすることになります。このため、BIGINT プロパティは IDENTITY および INT データ型だけに制限されます。

SELECT..INTO

既存の IDENTITY 列が新しいテーブルに選択された場合は、次の条件のいずれかが満たされている場合を除き、新しい列には IDENTITY プロパティが継承されます。

SELECT ステートメントに結合が含まれている。
複数の SELECT ステートメントが UNION を使用して結合されている。
IDENTITY 列が SELECT リストに 2 回以上指定されている。
IDENTITY 列が式の一部である。

これらの条件が 1 つでも満たされている場合は、列に IDENTITY プロパティは継承されず、代わりに NOT NULL として作成されます。

CREATE TABLE AS SELECT

CREATE TABLE AS SELECT (CTAS) は、SELECT..INTO に対して記述されているのと同じ SQL Server 動作に従います。ただし、ステートメントの CREATE TABLE 部分の列定義で IDENTITY プロパティを指定することはできません。また、CTAS の SELECT 部分で IDENTITY 関数を使用することもできません。テーブルに値を設定するには、CREATE TABLE を使ってテーブルを定義した後、INSERT..SELECT で値を設定する必要があります。

IDENTITY 列に明示的な値を挿入する

専用 SQL プールでは SET IDENTITY_INSERT <your table> ON|OFF 構文がサポートされています。この構文を使用して、IDENTITY 列に値を明示的に挿入できます。

多くのデータモデラーは、ディメンションの特定の行に定義済みの負の値を使うことを好みます。たとえば、-1 や unknown member 行です。

次のスクリプトでは、SET IDENTITY_INSERT を使用してこの行を明示的に追加する方法を示します。

SET IDENTITY_INSERT dbo.T1 ON;

INSERT INTO dbo.T1
(   C1,
    C2
)
VALUES (-1,'UNKNOWN');

SET IDENTITY_INSERT dbo.T1 OFF;

SELECT     *
FROM    dbo.T1;

データの読み込み

IDENTITY プロパティが存在すると、データ読み込みコードに影響があります。このセクションでは、IDENTITY を使用してテーブルにデータを読み込む場合のいくつかの基本的なパターンを示します。

IDENTITY を使用してテーブルにデータを読み込んで代理キーを生成するには、テーブルを作成した後、INSERT..SELECT または INSERT..VALUES を使用して読み込みを実行します。

次の例では基本的なパターンを示します。

--CREATE TABLE with IDENTITY
CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1),
     C2 VARCHAR(30)
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

--Use INSERT..SELECT to populate the table from an external table
INSERT INTO dbo.T1
(C2)
SELECT     C2
FROM    ext.T1;

SELECT *
FROM   dbo.T1;

DBCC PDW_SHOWSPACEUSED('dbo.T1');

Note

現在、IDENTITY 列のあるテーブルへのデータの読み込みに、CREATE TABLE AS SELECT を使用することはできません。

データの読み込みの詳細については、専用 SQL プール向けの抽出、読み込み、変換 (ELT) の設計と読み込みのベストプラクティスに関するページを参照してください。

システムビュー

sys.identity_columns カタログビューを使用して、IDENTITY プロパティを持つ列を識別できます。

データベーススキーマを理解しやすいように、次の例では sys.identity_columns を他のシステムカタログビューと統合する方法を示します。

SELECT  sm.name
,       tb.name
,       co.name
,       CASE WHEN ic.column_id IS NOT NULL
             THEN 1
        ELSE 0
        END AS is_identity
FROM        sys.schemas AS sm
JOIN        sys.tables  AS tb           ON  sm.schema_id = tb.schema_id
JOIN        sys.columns AS co           ON  tb.object_id = co.object_id
LEFT JOIN   sys.identity_columns AS ic  ON  co.object_id = ic.object_id
                                        AND co.column_id = ic.column_id
WHERE   sm.name = 'dbo'
AND     tb.name = 'T1'
;

制限事項

次の場合、IDENTITY プロパティを使用することはできません。

列のデータ型が INT または BIGINT ではない場合
列が分散キーでもある場合
テーブルが外部テーブルである場合

次の関連する関数は、専用 SQL プールではサポートされません。

一般的なタスク

次のサンプルコードを使用して、IDENTITY 列を操作するときに一般的なタスクを実行できます。

次のすべてのタスクで、列 C1 は IDENTITY です。

テーブルに割り当てられた最も高い値を見つける

分散テーブルに割り当てられた最も高い値を特定するには、MAX() 関数を使います。

SELECT MAX(C1)
FROM dbo.T1

IDENTITY プロパティのシードと増分を調べる

カタログビューで次のクエリを使って、テーブルの ID 増分とシード構成値を調べることができます。

SELECT  sm.name
,       tb.name
,       co.name
,       ic.seed_value
,       ic.increment_value
FROM        sys.schemas AS sm
JOIN        sys.tables  AS tb           ON  sm.schema_id = tb.schema_id
JOIN        sys.columns AS co           ON  tb.object_id = co.object_id
JOIN        sys.identity_columns AS ic  ON  co.object_id = ic.object_id
                                        AND co.column_id = ic.column_id
WHERE   sm.name = 'dbo'
AND     tb.name = 'T1'
;

次の方法で共有

専用 SQL プールで IDENTITY を使用して代理キーを作成する

代理キーとは

IDENTITY 列があるテーブルを作成する

値の割り当て

非対称のデータ

SELECT..INTO

CREATE TABLE AS SELECT

IDENTITY 列に明示的な値を挿入する

データの読み込み

システムビュー

制限事項

一般的なタスク

テーブルに割り当てられた最も高い値を見つける

IDENTITY プロパティのシードと増分を調べる

フィードバック

その他のリソース

次の方法で共有

専用 SQL プールで IDENTITY を使用して代理キーを作成する

代理キーとは

IDENTITY 列があるテーブルを作成する

値の割り当て

非対称のデータ

SELECT..INTO

CREATE TABLE AS SELECT

IDENTITY 列に明示的な値を挿入する

データの読み込み

システム ビュー

制限事項

一般的なタスク

テーブルに割り当てられた最も高い値を見つける

IDENTITY プロパティのシードと増分を調べる

関連するコンテンツ

フィードバック

その他のリソース

システムビュー