IDENTITY と SEQUENCE についてちょっとだけ書いた

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 の実装

なんか久しぶりにブログ書いたけどまとまりないなー。

*1:インクリメントする値が 正/負 によって変わる

*2:Heckaton の方が伝わりやすい?