インスパイア元はtatsumihrさんのBlogです。
最近感動したこと
このエントリを元にけろさんと話をしていたら、流れで「SQL ServerのIdentityについて纏めなさい」と命ぜられてしまいました。
ですので、また自分なりに纏めてみたいと思います。
IDENTITYプロパティ
SQL Serverに一意のIDを生成させる方法として、列にIDENTITYプロパティを指定する方法があります。
IDENTITYプロパティには次の特徴があります。
- ひとつのテーブルのひとつの列にしか指定できません。
- 指定する列のデータ型はint型、bigint型、smallint型、tinyint型、decimal型、numeric型に限ります。
- （設定により変更可能ですが）基本的に値のセットは出来ません。
IDENTITYを指定した列に値を挿入したい場合は、IDENTITY_INSERTプロパティをONに設定します。
この設定は同一セッション内でのみ有効です。
新しく付与されたIDの取得方法
IDENTITYプロパティにより新しく生成されたIDを取得する方法には次の３つの手段があります。
- @@IDENTITY
- SCOPE_IDENTITY
- IDENT_CURRENT
いずれも、生成されたIDを取得すると言う意味では同様のものではありますが、それぞれに特徴があります。
- @@IDENTITYはそれを指定したセッション内の任意のスコープにて最後に生成されたIDを返します。
- SCOPE_IDENTITYはそれを指定したスコープ内およびセッション内で最後に生成されたIDを返します。
- IDENT_CURRENTは任意のスコープ、任意のセッションにおいて特定テーブルに対して最後に生成されたIDを返します。
一見すると同じものを返す様に見えますが、「テーブルに新しく行を追加した結果、生成されたIDを取得したい」場合には注意が必要です。
例えば、@@IDENTITYは任意のスコープ（モジュールやストアドプロシージャなどの単位）で生成されたIDを返すので、トリガによりIDENTITYプロパティを指定したテーブルへの挿入をしていた場合には、トリガにより生成されたIDを返してしまいます。
また、IDENT_CURRENTはテーブルを限定しますがセッションを限定しないので、対象テーブルを更新するトランザクションが同時実行された場合に意図しないIDを返す恐れがあります。さらにスコープを限定しないので、トリガにより自身のテーブルに列を挿入する場合にも意図しないIDを返す恐れもあります。
従い、「テーブルに新しく行を追加した結果、生成されたIDを取得したい」と言う要件に対する解決策としてはSCOPE_IDENTITYを使うことが最善と言えます。
SCOPE_IDENTITYを取得する際は、SELECT SCOPE_IDENTITY で取得できます。
IDの生成にnewid関数を利用する方法
一意の値をSQL Serverに生成させる方法として、他にnewid関数を利用する方法もあります。
newid関数は、uniqueidentifier型の一意な値（GUID）を生成する関数です。
このnewid関数をuniqueidentifier型の列の規定値にすると、値が指定されなかった場合に新しいGUIDを生成してくれます。
データ型も違いますのでIDENTITYプロパティとはまったく異なるものですが、一意の値を生成すると言う目的に対しては有効です。
IDの生成にnewidを使用する場合の特徴は次の通りです。
- newid関数自体はひとつのテーブルの複数の列で指定可能です。
- 規定値にnewidを指定できる列のデータ型はuniqueidentifier型です。
- newidを規定値に指定していても、値のセットは可能です。
IDの生成にnewid関数を利用する事の利点と欠点
newidを規定値に指定していてもデフォルトで値の挿入が可能です。
しかもIDENTITY列と異なり、プログラム側からSystem.Guid.NewGuid()メソッドで挿入するIDを生成できるので、SCOPE_IDENTITY等を利用して事後にIDを取得する必要はありません。
もちろん、欠点もあります。
業務要件により「挿入された順序の把握が必要な場合」には、何らかの別項目が必要となります。
また、Guid.NewGuidで生成されたIDは理論上2^128分の1の確立で重複しますので、この確立が無視できない場合は相応の重複チェックが必要となります。
最後に
けろさん、こんな感じで纏めてみましたが如何でしょうか。
そうそう、もう宿題はご勘弁頂きたく...