IDENT_CURRENT / @@IDENTITY / SCOPE_IDENTITY の違い

MS SQL Server2000 の IDENTITY の値取得についてのメモ。IDENTITY は 2003-05-13 の「MS SQL Server のシーケンス型」で書いたように、連番というか一意な ID を DB 側で振り出すための仕組み。

- 結論。IDENT_CURRENT @@IDENTITY SCOPE_IDENTITY の用途別使い分け

IDENT_CURRENT @@IDENTITY SCOPE_IDENTITY の違い について、用途別にメモ。

トリガーなどで INSERT された値も含めた特定のテーブルの値を取りたいときは IDENT_CURRENT。
トリガーなどで INSERT された値も含めて取りたいときは @@IDENTITY。
トリガーなどで INSERT された値を除いて取りたいときは SCOPE_IDENTITY。

- IDENT_CURRENT のリファレンスはわかりにくい

リファレンスの説明を読んだが非常にわかりにくい。「任意のセッション」とか「有効範囲内」とか、説明なしでいきなり使われている言葉ばかり。任意って、誰の意志が反映されてるの? 結局何を指しているのかさっぱりわからない。

Transact-SQL リファレンス IDENT_CURRENT
http://www.microsoft.com/japan/msdn/library/ja/tsqlref/ts_ia ...

IDENT_CURRENT は、任意のセッションと有効範囲内の特定のテーブルに対して生成された最後の ID 値を返します。
@@IDENTITY は、すべての有効範囲を対象に、現在のセッション内の任意のテーブルに対して生成された最後の ID 値を返します。
SCOPE_IDENTITY は、現在のセッションと現在の有効範囲内の任意のテーブルに対して生成された最後の ID 値を返します。

訳文よりも原文の方がわかりやすいんじゃないかと思ったので、英語版を見てみる。

Transact-SQL Reference IDENT_CURRENT
http://msdn.microsoft.com/library/en-us/tsqlref/ts_ia-iz_82i ...

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

英語で書かれた方がわかりやすい。「任意」って any だったのか。all を「全て」と訳すのはまあいいけど。

IDENT_CURRENT は引数で指定した特定のテーブルについての値。全セッション、全スコープ。
@@IDENTITY は全てのテーブル、現在のセッション、全てのスコープ。
SCOPE_IDENTITY は全てのテーブル、現在のセッション、現在のスコープ。

- Transact-SQL リファレンス IDENT_CURRENT

例があるのは嬉しいけど、やっぱりわかりにくい。例を一つ一つ追いかけるよりも、英語のドキュメントを読んだ方がわかりやすい。

IDENT_CURRENT

任意のセッションと有効範囲内の特定のテーブルに対して生成された ID 値を返します。

構文
IDENT_CURRENT('table_name')

引数
table_name

ID 値が返されるテーブルの名前です。table_name は varchar であり、既定値はありません。

戻り値の型
sql_variant

解説
IDENT_CURRENT は、MicrosoftR SQL Server? 2000 の ID 関数である SCOPE_IDENTITY と @@IDENTITY に似ています。3 つの関数とも、最後に生成された ID 値を返します。ただし、各関数の中で、"最後" が定義される有効範囲とセッションがそれぞれ異なります。

IDENT_CURRENT は、任意のセッションと有効範囲内の特定のテーブルに対して生成された最後の ID 値を返します。


@@IDENTITY は、すべての有効範囲を対象に、現在のセッション内の任意のテーブルに対して生成された最後の ID 値を返します。


SCOPE_IDENTITY は、現在のセッションと現在の有効範囲内の任意のテーブルに対して生成された最後の ID 値を返します。
例
この例では、IDENT_CURRENT、@@IDENTITY、および SCOPE_IDENTITY がそれぞれ異なる ID 値を返します。

USE pubs
DROP TABLE t6
DROP TABLE t7
GO
CREATE TABLE t6(id int IDENTITY)
CREATE TABLE t7(id int IDENTITY(100,1))
GO
CREATE TRIGGER t6ins ON t6 FOR INSERT
AS
BEGIN
  INSERT t7 DEFAULT VALUES
END
GO
--end of trigger definition

SELECT  * FROM t6
--id is empty.

SELECT  * FROM t7
--id is empty.

--Do the following in Session 1
INSERT t6 DEFAULT VALUES
SELECT @@IDENTITY
/*Returns the value 100, which was inserted by the trigger.*/

SELECT SCOPE_IDENTITY()
/* Returns the value 1, which was inserted by the
INSERT stmt 2 statements before this query.*/

SELECT IDENT_CURRENT('t7')
/* Returns value inserted into t7, i.e. in the trigger.*/

SELECT IDENT_CURRENT('t6')
/* Returns value inserted into t6, which was the INSERT statement 4 stmts before this query.*/

-- Do the following in Session 2
SELECT @@IDENTITY
/* Returns NULL since there has been no INSERT action
so far in this session.*/

SELECT SCOPE_IDENTITY()
/* Returns NULL since there has been no INSERT action
so far in this scope in this session.*/

SELECT IDENT_CURRENT('t7')
/* Returns the last value inserted into t7.*/