Forum Discussion

hnarahari's avatar
hnarahari
Copper Contributor
Aug 15, 2023

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)
);

  • Hi hnarahari -- What version of SQL Server are you using in which you are encountering this issue?  Take care.

    • hnarahari's avatar
      hnarahari
      Copper Contributor
      Hi bake13,
      The SQL server version is "Microsoft SQL Server 2019 (RTM-GDR) (KB5021125) - 15.0.2101.7 (X64)".
      Thanks.
      • bake13's avatar
        bake13
        Icon for Microsoft rankMicrosoft

        Hi hnarahari -- Thank you for the information.  I'm not aware of a known issue.  Could you please outline the exact steps used to reproduce this behavior?  Take care.

Resources