歯抜けになったオートインクリメントを作り直す方法

開発＆デバッグを繰り返しているとテーブルにゴミがどんどんたまってしまいます。しかも性格が面倒臭がりなので入力するテストデータは勢い「ああああああ」や「いいいいいいい」や「000000000000000000」などが入って段々訳がわからなくなっていきます。「a」が7個のもしかないから今入れた8個のものは入ってないなぁとか考える始末

ある程度ゴミがたまったら、ばっさり削除してしまうのですが、これを繰り返している内にこまるのがオートインクリメントが設定したある場合です。

気がついたら、オートインクリメントされて順番になっているはずが歯抜けの飛び飛びの欠番がある状態になってしまいます。そこで、番号を振り直したいのですが、どうすれば良いのか・・・

そこはテスト環境と割り切って、歯抜けになった列をばっさり削除して削除した列と同じ列名で列を作成してオートインクリメントを設定しました。

参考：サンプルT-SQL
alter table dbo.human add id int identity(1,1);

これで、綺麗に1から始まった連番の列が再作成できます。

もちろん本番環境では、こんなことは通常出来ません。リレーションが崩れる可能性が高く、影響範囲を考えると恐ろしくてできません。歯抜けは気にしないのが一番。でも、どうしても歯抜けしたところは埋めたい場合はどうすれば良いのでしょう？

できない。無理。諦めよう。

と、本の数分前までは思っていました。でも、どうやら出来るようです。

「SQL Server 2008 オンライン ブック」を眺めていたら次のような記述がありました。

ID 列を持ったテーブルに対して頻繁に削除を行うと、ID 値間でギャップが生じる可能性があります。これが問題ならば、IDENTITY プロパティは使用しないでください。ギャップが生じないようにするには、また、既にあるギャップを埋めるには、IDENTITY_INSERT を ON に設定して明示的に値を入力する前に、既存の ID 値を評価してください。
IDENTITY (プロパティ) (Transact-SQL)

まず自分が歯抜けと表現した欠番が生じることを、本家ではギャップと呼称するようです。基本的には嫌だったら使用するなってのがポリシーみたいですが、一応解決策が提示されています。

削除した ID 値を再び使用する場合は、例 B のサンプル コードを使用して、次に使用可能な ID 値を検索します。tablename、column_type、MAX(column_type) – 1 をそれぞれ、テーブル名、ID 列のデータ型、そのデータ型の数値の最高許容値 -1 と置き換えてください。 DBCC CHECKIDENT を使用して現在の ID 値を調べ、その値を ID 列の最大値と比較します。
IDENTITY (プロパティ) (Transact-SQL)

まず、「SET IDENTITY_INSERT (Transact-SQL)」があり、これをオンにしているセッション内では、IDENTITY_INSERTが設定されている列にも値を指定して代入することが出来るようです。このオプションを使って、歯抜け(ギャップ)があって欠番している番号を調べて、それをInsertすればOKという話です。

SET IDENTITY_INSERT tablename ON
DECLARE @minidentval column_type
DECLARE @maxidentval column_type
DECLARE @nextidentval column_type
SELECT @minidentval = MIN($IDENTITY), @maxidentval = MAX($IDENTITY)
    FROM tablename
IF @minidentval = IDENT_SEED('tablename')
   SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('tablename')
   FROM tablename t1
   WHERE $IDENTITY BETWEEN IDENT_SEED('tablename') AND 
      @maxidentval AND
      NOT EXISTS (SELECT * FROM tablename t2
         WHERE t2.$IDENTITY = t1.$IDENTITY + 
            IDENT_INCR('tablename'))
ELSE
   SELECT @nextidentval = IDENT_SEED('tablename')
SET IDENTITY_INSERT tablename OFF

@nextidentvalに歯抜けした(ギャップのある)欠番が代入されています。これを、「SET IDENTITY_INSERT tablename OFF」の前にInSert文を追加してあげれば欠番が1つ解消できます。

ELSE
   SELECT @nextidentval = IDENT_SEED('human')

'####追加する
insert into human (name,old,,id) values('nora','15','man',@nextidentval)
'####追加した

SET IDENTITY_INSERT human OFF

ちなみに追加したInsert文の代わりに以下のInsert文を使ってみます。

insert into human values('nora','15','man',@nextidentval)

「列リストが使用されていて、IDENTITY_INSERT が ON のときに限り、テーブル 'human' の ID 列に明示的な値を指定できます。」とエラーが出てしまいます。列リストが何の話なのか一瞬わからなかったのですが、要はどの列に値を追加しようとしているのかを明示し無いと行けないって事です。「(name,old,,id)」という列指定がないので上記Insertではエラーが出るようです。

参考先はSQL Server 2008 オンライン ブックになっていますが、自分が試した環境は、XP PRO & SQL Server2005 (SP2) Developer EditionなのでSQL Server2005でも問題なく使用できます。

以上、先日の懇親会で隣の席の方と話がきっかけで調べたidentityでした。
テスト環境でidentity列をがっそり作り直すのは日常茶飯事ですが。。

参考情報

• SET IDENTITY_INSERT (Transact-SQL)
• IDENTITY (プロパティ) (Transact-SQL)
• INSERT (Transact-SQL)