Auto-increment my primary key: why 2 instead of 1?

Hi all,

I have a table where I have my ProdPK set up as Primary key, turned on "Is Identity" and set the Identity increment to 1. But each time I add a new item, the number incremented by 2... I have couple of other tables and they are all fine, just this particular table increased twice as it should. I check the setting against other tables and everything seems to be the same.

By the way, this is adding the data to the table inside MS SQL Server Management Studio manually. I haven't done anything in the ASP.NET page yet.

Thank you very much,

Kenny.

That's weird. I would double check the identity column settings, and maybe check for triggers on the table?

|||

Thanks. I did checked the table's setting and the auto-increment was set to "1". Other settings are identical to my other tables. Any suggestions?

Thanks again,

Kenny.

|||

Can you post the table script and the results you observe by executing "dbcc checkident ( YourTableName )" ? Have you made sure that there are no triggers defined on the table ?

|||

Here is the table's script:

1USE [C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.2\MSSQL\DATA\MTRENZ.MDF]2GO3/****** Object: Table [dbo].[T_PRODUCTS] Script Date: 12/22/2007 11:58:52 ******/4SET ANSI_NULLS ON5GO6SET QUOTED_IDENTIFIER ON7GO8SET ANSI_PADDING ON9GO10CREATE TABLE [dbo].[T_PRODUCTS](11[PROD_ID] [int] IDENTITY(1,1) NOT NULL,12[ACC_TYPE_ID_FK] [int] NOT NULL,13[PROD_NAME] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,14[PROD_DESCR] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,15[PROD_MODEL] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,16[PROD_STATUS] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,17[PROD_PRICE] [smallmoney] NULL,18[PROD_DATE_ADDED] [datetime] NOT NULL CONSTRAINT [DF_T_PRODUCTS_PROD_DATE_ADDED] DEFAULT (getdate()),19[PROD_ADDED_BY] [uniqueidentifier] NULL,20[PROD_NO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,21[PROD_NOTES] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,22[PROD_PIC_S] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,23[PROD_PIC_L] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,24[PROD_NEW] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,25 CONSTRAINT [PK_T_PRODUCTS] PRIMARY KEY CLUSTERED26(27[PROD_ID] ASC28)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]29) ON [PRIMARY]3031GO32SET ANSI_PADDING OFF33GO34USE [C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.2\MSSQL\DATA\MTRENZ.MDF]35GO36ALTER TABLE [dbo].[T_PRODUCTS] WITH NOCHECK ADD CONSTRAINT [ACT_TYPE_ID_FK] FOREIGN KEY([ACC_TYPE_ID_FK])37REFERENCES [dbo].[T_ACC_TYPES] ([ACC_TYPE_ID])38NOT FOR REPLICATION

This is what it return when I run the dbcc:

Checking identity information: current identity value '4', current column value '4'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Currently there are only two rows in my database, with the primary key values 2 and 4. It skipped 1 and 3.

Thank you,

Kenny.

|||

I don't know what exactly is the problem, but my database primary key started at 2, then 4, then 8... After 8, everything then become normal, meaning they increased correctly by 1!!!

I still don't know why, but it solved the problem itself I guess!

Thanks all,

Kenny.