identity_insert and table initialisation script

Question

i am trying to create a table with a row where id=0 but the identity insert column starts at 1,1 so all following use the identity insert

i have to execute

DBCC CHECKIDENT ('Foo', RESEED, 0);

after i manually insert (using a script) to get the next row to have id=1 is there another way to get id=0 for my first row which is from the insert script and all others that follow start at 1 so I dont have to call the dbcc checkident

create the following table

CREATE TABLE [dbo].[Foo](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CreatedAt] [datetime] NOT NULL CONSTRAINT DF_Foo_CreatedAt DEFAULT getdate(),
    [ModifiedAt] [timestamp] NOT NULL,
    [Code] [nvarchar](50) NOT NULL,
    [Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

i want a row which has an id=0 so i run the following next

SET IDENTITY_INSERT [dbo].[Foo] ON;
INSERT INTO [dbo].[Foo] ([Id],[Code],[Name]) VALUES (0, 'Default', 'Regular');
SET IDENTITY_INSERT [dbo].[Foo] OFF;

then i have to execute

DBCC CHECKIDENT ('Foo', RESEED, 0);

to get all following inserts to start from 1

Answer 1

Ben,

From what I understand you want the identity column to start with 0 instead of 1?

If that's what you want, you must change the initial seed IDENTITY(seed, increment)

change the create table to:

[Id] [int] IDENTITY(0,1) NOT NULL,

Answer 2

yeah, the choices seem to be identity(0,1) or DBCC IDENTCHECK. both seem to smell a little, so i thought I would throw it out there. Thanks for your response

Regards Ben

Answer 3

CHECKIDENT has some quirks:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/06/26/fun-with-dbcc-chekident.aspx