Post reply

Identity Insert Skips records

  • 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?

  • November 10, 2008 at 8:18 am

    #896332

    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

    tmpidi tmptext

    1 one

    2 two

    4 four

    5 five

    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!

  • November 10, 2008 at 10:24 pm

    #896726

    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

  • November 10, 2008 at 10:48 pm

    #896732

    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?

  • November 10, 2008 at 10:54 pm

    #896734

    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"

  • November 10, 2008 at 11:00 pm

    #896736

    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.

  • November 11, 2008 at 9:33 am

    #897021

    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 🙂

  • November 11, 2008 at 11:09 pm

    #897343

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

    "Keep Trying"

  • November 11, 2008 at 11:24 pm

    #897346

    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

  • November 12, 2008 at 11:48 am

    #897707

    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.

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

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