SQL ServerとSynapase Analytics 専用SQLプールのIDENTITY列の比較

概要

IDENTITYは自動で連番を採番し、レコードを管理する場合などで利用するカラム属性です。
設定した値に応じて、データがInsertされると番号が自動採番されます。
データウェアハウスシステムではサロゲートキーとして、テーブル内の行を一意に識別するために重複の無い識別子が必要となる場合が多く、このサロゲートキーとしてIDENTITYを使うことが多いです。

構文

IDENTITY(<最初の値>,<増分値>)

具体的には以下のような形でCREATE TABLEの際に指定します。

専用SQLプールでの作成例

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

SQL Serverと専用SQLプールのIDENTITY列の違い

SQL ServerとAzure Syanaps Analytics 専用SQLプールのIDENTITY列の主な違いは以下の通りです。

項目	SQL Server	専用SQLプール
一意な行の為の識別子としての利用	〇	〇
値の並び順	シーケンシャル	ランダム※1
サポートされるデータ型	Decimal, Int, Numeric, Smallint, Bigint, Tinyint	Int, BigInt
テーブル毎のIDENTITY列の最大数	1	1
@@IDENTITYシステム関数のサポート※2	はい	いいえ

上記の通りIDENTITY列はSQL ServerとAzure Syanaps Analytics 専用SQLプールの間で違いが存在しています。

※1 専用SQLプールでは採番の順序がランダム？

実際にはランダムではありません。Azure Syanaps Analytics 専用プールには60のディスク（ディストリビューション）が存在していますので、それぞれのディストリビューションにおいて値の重複が無いように値が採番されます。

例えば以下のテーブルと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);

上記の場合にはC1には1と61と言うデータがInsertされているのが確認できると思います。一見、増分値を1で指定しているので不具合のようにも見えますが、これは仕様です。この仕様の為にあたかも値がランダムで採番されているように錯覚しますが厳密にはランダムではありません。

※2@@IDENTITYシステム関数のサポート

@@IDENTITYは現在のセッションで最後に挿入されたIDENTITY列の値を返すシステム関数ですが、これはSynapase Analytics 専用SQLプールでは利用することが出来ません。

その他にもIDENTITY列に関する以下の関数が専用SQLプールではサポートされていません。

IDENTITY()
@@IDENTITY
SCOPE_IDENTITY
IDENT_CURRENT
IDENT_INCR
IDENT_SEED

その他制限事項

その他にも専用SQLプールでは以下のIDENTITYの利用に以下の制限事項があります。

• IDENTITY列はディストリビューションキーには使えない。
• テーブルが外部テーブルである場合は使えない。