SQL:テーブルのIdentitySeedを動的に設定したい

この文書はSQL Server 2000 SP4, SQL Server 2005 CTP Apr/2005時点をベースに記述されています。

クエリでテーブル(一時テーブルを含む)を作成する場合に、Identityを利用するテーブルを作ることも多いでしょう。

Create Table [#AAA]
(
  [key] int not null identity(1, 1),
  [data] nvarchar(10)
)

このように決定的なSeedを設定すると無事Identityを利用するテーブルを作成できます。

しかし・・・

declare @IdentitySeed int
set @IdentitySeed = 50

Create Table [#AAA]
(
  [key] int not null identity(@IdentitySeed, 1),
  [data] nvarchar(10)
)

このようにIdentity Seedを変数にすることは出来ません。

サーバー : メッセージ 170、レベル 15、状態 1、行 6
行 6: '@IdentitySeed' の近くに無効な構文があります。

解決策としては下記の2つの方法があります。

declare @IdentitySeed int
set @IdentitySeed = 50

Create Table [#AAA]
(
  [key] int not null identity(1, 1),
  [data] nvarchar(10)
)
DBCC CHECKIDENT ([#AAA], RESEED, @IdentitySeed)

米田さんが教えてくれたDBCC CHECKIDENTを使う方法です。一度作成するときには適当な値で設定しておく必要がありますが、即座にDBCC CHECKIDENTすれば、その値になります。

declare @IdentitySeed int
set @IdentitySeed=50

declare @CreateQuery nvarchar(4000)

set @CreateQuery = '
Create Table [##AAA]
(
  [key] int not null Identity(' + Convert(nvarchar, @IdentitySeed) + ', 1),
  [data] nvarchar(10)
)'
exec(@CreateQuery)

これはBryanさんの案です。

通常の動的SQLで解こうという案です。

ただし一時テーブルでもグローバル一時テーブルにしないといけないし、文字数を考えておかないといけないので、ちょっとデメリットも多いです。

SQL Server 2005になると、nvarchar(max)という構文が使えるようになるので、長いテーブル構築でも出来るようになるので、ややデメリットが減ります。

とりあえずの解決策はありますが、対応してほしいですね。