Identity Insert Skips records

Question

Post reply

Identity Insert Skips records

Chris Hornung-847288

SSC-Addicted

Points: 479
More actions
November 10, 2008 at 7:46 am

#383341

I have an issue with a web application using a newly installed SQL Server
2005 database. All tables have an ID field that has an identity insert
assigned. The base is 1 and the increment 1.
A web application allows users to insert new records into the tables.
Updates and deleted are not written into the application at all. All user
input is passed to SQL through stored procedures.
Several tables are missing either a record or groups of records, where the
ID field can skip 20 records or more. That ist ID 1-20 will be there but the
next value is 34, or 21, or something else. Its as though someone deleted
the records, but I can pretty safely rule that out.
And I have had users claim they have entered data that is missing. So it
appears the they are adding the in the web application.
Has anyone had a similar experience?
Any ideas?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you might need to review your web app for errors....

the identity is incremented even if there is an error in your code.

here's an example: the third insert will fail....the value for 3 got "used" by the attempted insert, and so the table only has values for 1,2,4,5:

create table tmp(tmpidi int identity(1,1) primary key,tmptext varchar(10) )

insert into tmp(tmptext)

select 'one'

union

select 'two'

insert into tmp(tmptext)

select 'three is too large for the definition of field'

insert into tmp(tmptext)

select 'four'

union

select 'five'

select * from tmp

tmpiditmptext

1one

2two

4four

5five

an error on insert

chrish (11/10/2008)
I have an issue with a web application using a newly installed SQL Server
2005 database. All tables have an ID field that has an identity insert
assigned. The base is 1 and the increment 1.
A web application allows users to insert new records into the tables.
Updates and deleted are not written into the application at all. All user
input is passed to SQL through stored procedures.
Several tables are missing either a record or groups of records, where the
ID field can skip 20 records or more. That ist ID 1-20 will be there but the
next value is 34, or 21, or something else. Its as though someone deleted
the records, but I can pretty safely rule that out.
And I have had users claim they have entered data that is missing. So it
appears the they are adding the in the web application.
Has anyone had a similar experience?
Any ideas?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

krayknot SSCertifiable Points: 7998 More actions · Answer 2

chrish (11/10/2008)
I have an issue with a web application using a newly installed SQL Server
2005 database. All tables have an ID field that has an identity insert
assigned. The base is 1 and the increment 1.
A web application allows users to insert new records into the tables.
Updates and deleted are not written into the application at all. All user
input is passed to SQL through stored procedures.
Several tables are missing either a record or groups of records, where the
ID field can skip 20 records or more. That ist ID 1-20 will be there but the
next value is 34, or 21, or something else. Its as though someone deleted
the records, but I can pretty safely rule that out.
And I have had users claim they have entered data that is missing. So it
appears the they are adding the in the web application.
Has anyone had a similar experience?
Any ideas?

There must be a flaw i nthe web app code. ID field cannot skips its sequence

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

krayknot (11/10/2008)

There must be a flaw i nthe web app code. ID field cannot skips its sequence

Actually - that's not at all the case. There are many ways gaps could appear in identity generations (transaction rollbacks for one). There is ultimately no reliable way I know of to 100% guarantee you won't have gaps.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

Identity values are "skipped/missed" when there is an error in your insert statement. Even though the insert statement fails the identity value gets used.

"Keep Trying"

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 5

do all types of validations at front-end, this is one of the best validation, validation like length, datatype, etc.

If any error occurs, the identity column increment the value.

Otherwise, in you SP, you can take max (id) and increment by 1 and then insert. This is also one of the best option.

use begin tran & commit or Rollback things in your sp.

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 6

Kishore.P (11/10/2008)
do all types of validations at front-end, this is one of the best validation, validation like length, datatype, etc.
If any error occurs, the identity column increment the value.
Otherwise, in you SP, you can take max (id) and increment by 1 and then insert. This is also one of the best option.

I agree you should validate before trying to insert.. but, that being said.. I dont think taking max(id) + 1 is the best way to go.. you COULD run into the case where you have 2 inserts at the exact same time which both grab for example 32 as the id, then add one and both try to insert 33.. of course.. the odds against it are huge..

why does it matter if the identity column skips anyway? its just a unique value.. aesthetically it can be a pain in the butt, but otherwise *shrug*

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

ChiragNS One Orange Chip Points: 26137 More actions · Answer 7

Yes max(id) + 1 is not the best way to go. I would prefer identity columns to this approach.

"Keep Trying"

SqlUser-369680 Old Hand Points: 384 More actions · Answer 8

Hi,

I believe the problem is in the program itself, try to put some trap or validation before insert.

Allow me to ask further with Identity column.

Is Identity column the same as rowid? with skip 1 as the constraints.

Is there a limit in Identity columns, for me I define it as Integer but in my forecast the number of record that will be inserted into the table will reach 2M in a year. Will my insert fail?

Thanks

jbuttery Ten Centuries Points: 1226 More actions · Answer 9

Let the server handle the identity column, not your app. Do not set Identity_Isert to ON. Insert your data and SQL will increment the identity column.