Forum Discussion
Identity column set to seed from 1 inserts 0 in sql server
Hi,
I have a peculiar problem when the first record is inserted into "[dbo].[EQP_CRITICALITY]" table.
When the first record is inserted into this table, the Identity PK column has 0, but it was expected to be 1. This is observed in one particular database backup only.
If I create a new test database with the same create table script, and insert the first record, the PK value is 1.
A few blogs mention this could be a know SQL issue:
- Identity Column set to Seed at 1, inserts first row with ID == 0 (microsoft.com)
- Programmer`s thoughts: Identity starts from 0 instead of 1. SQL Server bug? (pro-thoughts.blogspot.com)
Is this an known SQL bug?
If so, then in which version this was resolved?
My table definition is as below:
CREATE TABLE [dbo].[EQP_CRITICALITY] (
[Eqp_Criticality_Id] INT IDENTITY (1, 1) NOT NULL,
[Eqp_Criticality_Code] VARCHAR (10) NOT NULL,
[Eqp_Criticality_Desc] VARCHAR (50) NOT NULL,
[Last_Mod_By_User_ID] INT CONSTRAINT [DF__EQP_CRITI__Last___51140748] DEFAULT ((0)) NOT NULL,
[Last_Mod_Date] DATETIME CONSTRAINT [DF__EQP_CRITI__Last___52082B81] DEFAULT (getdate()) NOT NULL,
[Create_By_User_ID] INT CONSTRAINT [DF__EQP_CRITI__Creat__52FC4FBA] DEFAULT ((0)) NOT NULL,
[Create_Date] DATETIME CONSTRAINT [DF__EQP_CRITI__Creat__53F073F3] DEFAULT (getdate()) NOT NULL,
CONSTRAINT [PK_EQP_CRITICALITY] PRIMARY KEY CLUSTERED ([Eqp_Criticality_Id] ASC) WITH (FILLFACTOR = 90),
CONSTRAINT [UNQ_Eqp_Criticality] UNIQUE NONCLUSTERED ([Eqp_Criticality_Code] ASC) WITH (FILLFACTOR = 90)
);