I have a SQL Server 2008 server (build 10.0.5500). Earlier this week I ran this on a table that already had data in it:
delete from dbo.table go dbcc checkident('dbo.table',reseed,0)
When the user went to create a new record later on, somehow an ID of 0 was inserted into the ID column, instead of the 1 SQL Server usually puts in if identity(1,1) is configured for the ID.
This caused some weird issues, but clearing the data and running the reseed resulted in a 1 being inserted, as expected. I can't duplicate the issue.
For reference, here's the general format for our save sp's:
alter procedure dbo._TableSave @pk_id int, @field varchar(50) as if (@pk_id is null) begin set nocount on; insert into dbo.Table ( Field ) values ( @field ); select scope_identity(); end else begin update dbo.Table set Field=@field where PK_ID=@pk_id select @pk_id end
Does anyone know what could cause SQL Server to insert a 0 in the ID when it should have been a 1?
We are not inserting data into identity columns (with identity_insert on) anywhere in the application.