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

• Fabric無料試用版を開始します。
• Fabric Data Warehouse 用マイグレーションアシスタント

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

代理キーとは

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

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 にのみアクセスすることになります。 このため、IDENTITY プロパティは INT および BIGINT データ型だけに制限されます。

SELECT..INTO

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

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

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

SELECT としてテーブルを作成する

CREATE TABLE AS SELECT (CTAS) は、SELECT..INTO に対して記述されているのと同じ SQL Server 動作に従います。 ただし、ステートメントの IDENTITY 部分の列定義で CREATE TABLE プロパティを指定することはできません。 また、CTAS の IDENTITY 部分で SELECT 関数を使用することもできません。 テーブルに値を設定するには、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');

データの読み込みの詳細については、専用 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()
• @@IDENTITY
• SCOPE_IDENTITY
• IDENT_CURRENT
• IDENT_INCR
• IDENT_SEED

一般的なタスク

次のサンプル コードを使用して、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 プールを使用してテーブルを設計する
• CREATE TABLE (Transact-SQL) IDENTITY (プロパティ)
• DBCC CHECKIDENT(Transact-SQL)