SQL ServerのIDENTITY値

基本事項

IDENTITY値にはSEED(初期値), INCR(増分)という概念がある。
また、IDENT_????という関数が用意されている(以下)

IDENT_CURRENT　現在値
IDENT_SEED　初期値
IDENT_INCR　増分

CREATE TABLE foo (
  [ID] INT IDENTITY(25, 2) PRIMARY KEY,
  [NAME] NVARCHAR(16) NOT NULL,
  [AGE] INT
);

INSERT INTO foo (NAME) VALUES (N'あ'), (N'い'), (N'う');

SELECT * FROM foo;

ID NAME
25 あ
27 い
29 う

SELECT
  IDENT_CURRENT('foo') [Current],
  IDENT_SEED('foo') [Seed],
  IDENT_INCR('foo') [Incr]
;

Current Seed Incr
29 25 2

IDENTITY列に明示的に値を指定して挿入する

通常、IDENTITYが設定されている列に値を明示的に指定して挿入はできない。
これがやりたいときは SET IDENTITY_INSERT ON を予め実行する必要がある。

オートインクリメント列を事前に採番しておきたい場合

テーブルに共有ロック(SELECTは許すがUPDATE, INSERT, DELETEを許さない)をかけてから先述のIDENT_????関数を使って採番。

DECLARE @items TABLE ([ID] INT NOT NULL, NAME NVARCHAR(16) NOT NULL, AGE INT);

BEGIN TRANSACTION;

WITH X (NAME, AGE) AS (
  SELECT N'わ', NULL
  UNION ALL SELECT N'を', NULL
  UNION ALL SELECT N'ん', 13
)
INSERT INTO @items ([ID], NAME, AGE)
SELECT
  IDENT_CURRENT('foo') + (IDENT_INCR('foo') * ROW_NUMBER() OVER (ORDER BY [NAME])),
  X.NAME,
  X.AGE
FROM
  X WITH (TABLOCK, HOLDLOCK)  -- 共有ロック。トランザクション終了まで有効
;

SET IDENTITY_INSERT foo ON;

INSERT INTO foo ([ID], NAME, AGE) SELECT [ID], NAME, AGE FROM @items;

COMMIT;

名称を指定せずテーブルのIDENTITY列を参照する -- $IDENTITY

MSDNのサンプルの中に $IDENTITY というキーワードがあって、最初何を表すのかわからなかった。

これは対象としたテーブルのIDENTITY列を指すキーワードらしい。

SELECT $IDENTITY FROM foo

ID
25
27
29
31
33
35

参考リンク

IDENTITY (プロパティ) (Transact-SQL)
IDENT_SEED (Transact-SQL)
ロックヒント
 Usage of $ROWGUID and $IDENTITY in SQL Server

今川館

都内勤務の地味OLです