インサートで生成したばかりのID値を取得するにはどれが最適でしょうか?また、これらのステートメントのパフォーマンス面での影響は?
1.scope_identity()
2.集約関数 MAX()
3.SELECT TOP 1
IdentityColumn FROM TableName ORDER BY IdentityColumn DESC
.
1行を挿入する際に、生成されたIDを取得したい場合は、SCOPE_IDENTITY()
を使用してください。
CREATE TABLE #a(identity_column INT IDENTITY(1,1), x CHAR(1));
INSERT #a(x) VALUES('a');
SELECT SCOPE_IDENTITY();
結果です。
----
1
複数の行を挿入していて、生成されたIDのセットを取得する必要がある場合は、OUTPUT
句を使用してください。
INSERT #a(x)
OUTPUT inserted.identity_column
VALUES('b'),('c');
結果です。
----
2
3
また、なぜこれが一番早い選択肢なのでしょうか?
パフォーマンスはさておき、デフォルトの分離レベルや複数のユーザーがいる場合に正しいことが保証されているのはこれらだけです。正しさの面を無視しても、SQL ServerはSCOPE_IDENTITY()
で挿入された値をメモリ上に保持していますので、当然ながら、そのテーブルやシステムテーブルに対して独自の分離クエリを実行するよりも高速になります。
正しさの側面を無視することは、郵便配達人に「今日の郵便物の配達はよくやった」と言うようなものです。彼は平均時間よりも10分早く配達を終えましたが、問題はどの郵便物も正しい家に配達されなかったことです。
以下のものは使用しないでください。
または
TOP 1- あなたが得た
MAX()が他の人'のものではないことを確実にするために、2つのステートメントをシリアライズ可能なアイソレーションで保護しなければなりません。これは単に
SCOPE_IDENTITY()`を使うよりもはるかにコストがかかります。これらの関数は、2行以上を挿入して、すべてのID値を生成する必要がある場合にも失敗します。
性能は別にして、それぞれ意味合いが異なります。
SCOPE_IDENTITY()`は、現在のスコープ内で直接あらゆるテーブルに挿入された最後のID値を与えます(スコープはバッチやストアドプロシージャなどですが、例えば現在のスコープで起動されたトリガー内ではありません)。
IDENT_CURRENT()`は、任意のスコープから、任意のユーザによって、特定のテーブルに挿入された最後のID値を与えます。
IDENTITY` は、テーブルやスコープに関係なく、現在の接続の最新の INSERT 文によって生成された最後の ID 値を示します。(余談ですが、Accessはこの関数を使用しているため、IDカラムを持つテーブルに値を挿入するトリガーに問題があります)。
MAX()や
TOP 1を使用すると、テーブルに負のIDステップがある場合や、
SET IDENTITY_INSERT`を使用して行が挿入された場合に、まったく間違った結果が得られます。以下に、これらを実演するスクリプトを示します。
CREATE TABLE ReverseIdent (
id int IDENTITY(9000,-1) NOT NULL PRIMARY KEY CLUSTERED,
data char(4)
)
INSERT INTO ReverseIdent (data)
VALUES ('a'), ('b'), ('c')
SELECT * FROM ReverseIdent
SELECT IDENT_CURRENT('ReverseIdent') --8998
SELECT MAX(id) FROM ReverseIdent --9000
SET IDENTITY_INSERT ReverseIdent ON
INSERT INTO ReverseIdent (id, data)
VALUES (9005, 'd')
SET IDENTITY_INSERT ReverseIdent OFF
SELECT IDENT_CURRENT('ReverseIdent') --8998
SELECT MAX(id) FROM ReverseIdent --9005
まとめ: SCOPE_IDENTITY()
, IDENT_CURRENT()
, または @@IDENTITY
を使用し、実際に必要なものを返すものを使用していることを確認してください。