中の技術日誌ブログ

C#とC++/CLIと
VBと.NETとWindowsで戯れる
 

目次

Blog 利用状況

ニュース

自己紹介

東京でソフトウェアエンジニアをやっています。
お仕事大募集中です。
記事執筆や、講師依頼とかでも何でもどうぞ(*^_^*)
似顔絵 MSMVPロゴ
MSMVP Visual C# Since 2004/04-2013/03

記事カテゴリ

書庫

日記カテゴリ

00-整理

01-MSMVP

DBCC CHECKIDENT RESEEDはいつも同じとは限らない(SQL Server 2000 or 2005)

UnitTest用にDBの枠組みから毎度毎度作り上げるようにしているのですが、そうそうCreate Databaseからやっているときりが無いので、1回のテストの流れではDatabaseの作成は1回にとどめています。

その分テストの1回ごとに元の状態に戻してやる必要があります。

IdentityがSQL Serverの特徴ですので、基本的にはIdentity列を使うことにしているのですが、これの初期化にはDBCC CHECKIDENT を使います。

ただしこれ1度もテーブルにInsertされていない場合と、InsertしてからDeleteして再設定するときでは動きが違います。

CREATE TABLE [#Table_1](
  [ident] [int] IDENTITY(3,1) NOT NULL,
  [val] [nchar](10) COLLATE Japanese_CI_AS NULL
 ) ON [PRIMARY]
 
 --いきなりCHECKIDENT
 DBCC CHECKIDENT ('#table_1', reseed, 1)
 insert into [#table_1](val) values('めいしょう')
 insert into [#table_1](val) values('めいしょう')
 select * from #table_1
 
 --一度データを設定したものを削除して最INSERT
 delete [#table_1]
 DBCC CHECKIDENT ('#table_1', reseed, 1)
 insert into [#table_1](val) values('めいしょう')
 insert into [#table_1](val) values('めいしょう')
 select * from #table_1
 
 drop table [#table_1]

DBCCからINSERTしてSELECTの流れはまったく一緒のSQLですが、結果はまったく違います。

1 めいしょう 
2 めいしょう 

2 めいしょう 
3 めいしょう 

このように結果が違います。

なにもINSERTしない状態ではDBCCした直後にはそのDBCCで設定した値がそのまま使われますが、1度でもINSERTしていると1の次の値の2から利用されます。

DBCC CHECKIDENT の仕様を見る限り最大値を設定しますとのことなので、最初の動きがおかしいのは明白ですが、これはIdentity Seedが一度も設定されていないからnullに成っているのが原因と思われます。

でもnullは設定できないので不公平です。

こんなのおかしいよと思う人が1人でもいれば、MSDN Product Feedback にKatmai用にWishをあげたいと思います。どうでしょ?

#でもnullの時の動きが変わるとそれはそれで困るかもしれないんですがね~

単体テストする場合には事前に、またはCreateDatabaseするときに、一度ダミー行をINSERTして、Deleteしてまわって、DBCC CHECKIDENTしてまわるようにするといいと思います。

 

投稿日時 : 2005年10月10日 21:32

コメントを追加

# van cleef and arpels bracelet faux 2017/11/12 8:50 defzklpawefsztmzlvacw@hotmal.com

What does a “literal piece of G-d” mean?

# It's really a great and useful piece of information. I'm satisfied that you simply shared this helpful information with us. Please keep us up to date like this. Thanks for sharing. 2019/11/25 14:06 It's really a great and useful piece of informatio

It's really a great and useful piece of information. I'm satisfied that you simply shared
this helpful information with us. Please keep us up to date like this.
Thanks for sharing.

# Hey There. I found your weblog the use of msn. That is a really well written article. I will make sure to bookmark it and return to learn extra of your useful information. Thanks for the post. I will definitely return. 2019/11/26 9:36 Hey There. I found your weblog the use of msn. Tha

Hey There. I found your weblog the use of msn. That is a really well
written article. I will make sure to bookmark it and return to learn extra of your useful information. Thanks for the
post. I will definitely return.

# Hey There. I found your weblog the use of msn. That is a really well written article. I will make sure to bookmark it and return to learn extra of your useful information. Thanks for the post. I will definitely return. 2019/11/26 9:37 Hey There. I found your weblog the use of msn. Tha

Hey There. I found your weblog the use of msn. That is a really well
written article. I will make sure to bookmark it and return to learn extra of your useful information. Thanks for the
post. I will definitely return.

# Hey There. I found your weblog the use of msn. That is a really well written article. I will make sure to bookmark it and return to learn extra of your useful information. Thanks for the post. I will definitely return. 2019/11/26 9:38 Hey There. I found your weblog the use of msn. Tha

Hey There. I found your weblog the use of msn. That is a really well
written article. I will make sure to bookmark it and return to learn extra of your useful information. Thanks for the
post. I will definitely return.

# Hey There. I found your weblog the use of msn. That is a really well written article. I will make sure to bookmark it and return to learn extra of your useful information. Thanks for the post. I will definitely return. 2019/11/26 9:39 Hey There. I found your weblog the use of msn. Tha

Hey There. I found your weblog the use of msn. That is a really well
written article. I will make sure to bookmark it and return to learn extra of your useful information. Thanks for the
post. I will definitely return.

# I've been exploring for a little bit for any high quality articles or weblog posts on this sort of area . Exploring in Yahoo I ultimately stumbled upon this web site. Reading this information So i am happy to show that I have a very good uncanny feeling 2019/12/07 14:40 I've been exploring for a little bit for any high

I've been exploring for a little bit for any high quality
articles or weblog posts on this sort of area . Exploring in Yahoo I
ultimately stumbled upon this web site. Reading this information So i am happy to show that I have a very good uncanny
feeling I found out exactly what I needed.
I so much surely will make sure to don?t fail to remember this website
and give it a look on a relentless basis.

# I do believe all of the ideas you have offered to your post. They are really convincing and can definitely work. Still, the posts are too brief for beginners. May you please extend them a little from subsequent time? Thanks for the post. 2019/12/14 12:09 I do believe all of the ideas you have offered to

I do believe all of the ideas you have offered to your
post. They are really convincing and can definitely work.
Still, the posts are too brief for beginners. May you please extend them
a little from subsequent time? Thanks for
the post.

# I do believe all of the ideas you have offered to your post. They are really convincing and can definitely work. Still, the posts are too brief for beginners. May you please extend them a little from subsequent time? Thanks for the post. 2019/12/14 12:10 I do believe all of the ideas you have offered to

I do believe all of the ideas you have offered to your
post. They are really convincing and can definitely work.
Still, the posts are too brief for beginners. May you please extend them
a little from subsequent time? Thanks for
the post.

# I do believe all of the ideas you have offered to your post. They are really convincing and can definitely work. Still, the posts are too brief for beginners. May you please extend them a little from subsequent time? Thanks for the post. 2019/12/14 12:11 I do believe all of the ideas you have offered to

I do believe all of the ideas you have offered to your
post. They are really convincing and can definitely work.
Still, the posts are too brief for beginners. May you please extend them
a little from subsequent time? Thanks for
the post.

# I do believe all of the ideas you have offered to your post. They are really convincing and can definitely work. Still, the posts are too brief for beginners. May you please extend them a little from subsequent time? Thanks for the post. 2019/12/14 12:12 I do believe all of the ideas you have offered to

I do believe all of the ideas you have offered to your
post. They are really convincing and can definitely work.
Still, the posts are too brief for beginners. May you please extend them
a little from subsequent time? Thanks for
the post.

# 8 The host is Each room also 4 2020/01/01 0:26 Typicalcat27

http://www.eglatech.it/2019/08/17/%ef%bb%bfno-charge-newest-senior-online-dating-services/ where to meet italian singles in orlando free https://autoverhuurlelyrent.nl/truly-free-biggest-seniors-dating-online-sites-blogs.wankuma.com.pdf where to meet australian singles in vancouver https://balkan-danas.net/%ef%bb%bfcompletely-free-cheapest-seniors-dating-online-services/ jacksonville muslim mature dating online site http://mauzepow.de/mature-dating-online-website-no-monthly-fee-blogs.wankuma.com.pdf america american mature dating online service

# Hello there! I know this is kinda off topic but I'd figured I'd ask. Would you be interested in exchanging links or maybe guest authoring a blog article or vice-versa? My blog addresses a lot of the same subjects as yours and I feel we could greatly benef 2020/01/05 4:52 Hello there! I know this is kinda off topic but I'

Hello there! I know this is kinda off topic but I'd figured I'd
ask. Would you be interested in exchanging links or maybe guest
authoring a blog article or vice-versa? My blog addresses
a lot of the same subjects as yours and I feel we could greatly benefit from each other.
If you might be interested feel free to send me an e-mail.
I look forward to hearing from you! Superb blog by the way!

タイトル  
名前  
URL
コメント