This issue caught me out today whilst putting together some build scripts for reference data. So I thought I’d share it.
USE [tempdb]
GO
CREATE TABLE [dbo].[Table_1](
[Col1] [int] IDENTITY(1,1) NOT NULL,
[Col2] [char](1) NOT NULL
) ON [PRIMARY]
GO
delete from dbo.table_1
dbcc checkident(Table_1,Reseed,0)
insert into table_1
values('1')
--Checking identity information: current identity value 'NULL', current column value '0'.
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Select * from table_1
Basically when you first create a table with an identity column the value of the identity prior to inserting any data is NULL and not 0.
Therefore the first DBCC CHECKIDENT does not behave as expected.
The first row you write has an identity value of 0 and not 1.
Now Run it again and the problem magically disappears…(the helpful messages in green didn’t come out in the build scripts – at least I didn’t see them.)
delete from dbo.table_1
dbcc checkident(Table_1,Reseed,0)
insert into table_1
values('1')
--Checking identity information: current identity value '0', current column value '0'.
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Select * from table_1
drop table dbo.table_1
I should also point out that I was using Database Edition and this code was in my post-deployment script. I therefore didn't see the messages as it was all executed in msbuild. What was even more interesting was that the build scripts carried on regardless even though as a result of my bug a foreign key violation occurred. More on that in another post methinks...
Cheers, James