Identity column set to seed from 1 inserts 0 in sql server

Copper Contributor

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...

 

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

3 Replies

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

Hi @bake13,
The SQL server version is "Microsoft SQL Server 2019 (RTM-GDR) (KB5021125) - 15.0.2101.7 (X64)".
Thanks.

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.