IDENTITY プロパティ使用時の動作について - Microsoft SQL Server Japan Support Team Blog - Site Home - MSDN Blogs を見かけたので読んでみた。
IDENTITY (プロパティ) (Transact-SQL) にも載ってる内容が多いなかで、キャッシュする値の数については、MSDN にも載ってなかったのでへーって感じ。
IDENTITY の話しを見たついでにちょこちょこ触ってみることに。
IDENTITY 列の値が、列のサイズを超える場合はオーバーフローになったような記憶があったので試してみた。
4回 INSERT したら溢れるテーブルを作成して INSERT してみる。
create table Tab1 ( id int identity(2147483645, 1) not null, hoge int not null ) create table Tab2 ( id decimal(38, 0) identity(99999999999999999999999999999999999997, 1) not null, hoge int not null ) go insert into Tab1 (hoge) output inserted.* values(1) go 4 insert into Tab2 (hoge) output inserted.* values(1) go 4
やっぱりオーバーフローだった。
MSDN には、連続する値が欲しければ 2012 から追加された SEQUENCE を NO CHACE にして使ってねと書いてるので、SEQUENCE もちょっと触っとく。
CREATE SEQUENCE (Transact-SQL)
SEQUENCE も 取った値を使わない(Rollback 等)場合は、不連続な値になるのは IDENTITY と一緒。
IDENTITY と違うのは、最大値 or 最小値 に達した後に次の値を要求すると CYCLE 指定してたら 最小値 or 最大値 に戻って繰り返す。
開始値では無く、最小値 or 最大値*1 なのは要注意かな。
create sequence seq_hoge start with 5 increment by -1 minvalue 3 maxvalue 10 cycle go select next value for seq_hoge go 4
NO CYCLE だと、11728 だかのエラーが返ってくる。
SEQUENCE って負にインクリメント出来たのか〜!という事で IDENTITY でも出来るか試したら出来た。
create table Tab3 ( id int identity(100, -1) not null, hoge int not null ) go insert into Tab3 (hoge) output inserted.* values (1) go 3
SEQUENCE はまとめてN個とるストアドも用意されてる。
sp_sequence_get_range (Transact-SQL)
CYCLE してる場合は、ちゃんと値を取るの面倒そう。
IDENTITY の代わりに SEQUENCE を使うケースは、MSDNの シーケンス番号 に書かれてる。
これ以外にも、2014 から入った メモリ最適化テーブル*2 では、
IDENTITY(1, 1) しかサポートされてないのでそれ以外は、SEQUENCE 使ってねと。
メモリ最適化テーブルへの IDENTITY の実装
なんか久しぶりにブログ書いたけどまとまりないなー。