patternsqlModerate
What could cause the wrong ID to be inserted?
Viewed 0 times
thewhatcouldinsertedwrongcause
Problem
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:
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:
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.
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
endDoes 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.
Solution
Doing
This causes the next identity entered to be 10:
dbcc checkident('dbo.table',reseed,0) will cause the next entry in a newly created / truncated table to have 0 as the identity.CREATE TABLE TestIdent
(
ID INT NOT NULL CONSTRAINT PK_TestIdent PRIMARY KEY CLUSTERED IDENTITY(1,1)
, SomeText nvarchar(255)
);
dbcc checkident('dbo.TestIdent',reseed,0)
INSERT INTO TestIdent VALUES ('Test');
SELECT * FROM dbo.TestIdent;This causes the next identity entered to be 10:
TRUNCATE TABLE dbo.TestIdent;
DBCC CHECKIDENT('dbo.TestIdent',reseed,10)
INSERT INTO TestIdent VALUES ('Test');
SELECT * FROM dbo.TestIdent;Code Snippets
CREATE TABLE TestIdent
(
ID INT NOT NULL CONSTRAINT PK_TestIdent PRIMARY KEY CLUSTERED IDENTITY(1,1)
, SomeText nvarchar(255)
);
dbcc checkident('dbo.TestIdent',reseed,0)
INSERT INTO TestIdent VALUES ('Test');
SELECT * FROM dbo.TestIdent;TRUNCATE TABLE dbo.TestIdent;
DBCC CHECKIDENT('dbo.TestIdent',reseed,10)
INSERT INTO TestIdent VALUES ('Test');
SELECT * FROM dbo.TestIdent;Context
StackExchange Database Administrators Q#39824, answer score: 10
Revisions (0)
No revisions yet.