Idenity column question /issue

  • I have a huge list of 50 million or so. here is the basic schema

    id bigint IDENTITY(1,1),

    sn varchar(50)

    Create primary key pk_id on table(id)

    Create unique index ui_sn on table(sn)

    this is a brand new table, I have inserted over 50 million records into this table and it seemed the ID column was correct.

    But for some reason, over time, after doing alot of inserts into the table (into the field sn) that my ID gets out of order.

    Like for example select id from table where id > 4999989 and < 50000000

    i would see:

    4999990

    4999996

    4999997

    4999999

    When i would expect to see 10 results in order.

    I can assure you that i have run NO deletes what so ever, so why is my idenity column not staying in order????

  • gaps in an identity are normal. they happen when an error occurs and a transaction gets rolled back; although a transaction would not get committed, the incrementing of an identity still occurs.

    if this is happening, you should check your code to see why an insert got rolled back.

    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!

  • hi as per lowell told you need to check your code first ..and another thing if your inserted records have you count are they same...as in source table...may be when transactions rollback or like that you may face problem...try to insert data into slots...by putting where clause...

    Raj Acharya

  • Hi,

    Also there could be a posibility that few records must be deleted at the source table at some point of time. Later when this table gets exported to any destination table, then that table will also miss those identity values...

    Just an example:

    1. create table iden (Number int identity(1,1), Name char(10))

    insert into iden values ('AAA')

    insert into iden values ('BBB')

    insert into iden values ('CCC')

    insert into iden values ('DDD')

    insert into iden values ('EEE')

    select * from iden

    1AAA

    2BBB

    3CCC

    4DDD

    5EEE

    Now let us delete from record from this:

    delete iden where Name like 'BBB'

    insert into iden values ('XXX')

    select * from iden

    1AAA

    6XXX

    3CCC

    4DDD

    5EEE

    2. Now we will create a new table and import the data from teh above table.

    create table idencopy2 (Number int identity(1,1), Name char(10))

    SET IDENTITY_INSERT idencopy2 ON

    Insert into idencopy2(Number,Name) (select Number,Name from iden)

    select * from idencopy2

    1AAA

    6XXX

    3CCC

    4DDD

    5EEE

    select * from idencopy2 where number between 1 and 3

    1AAA

    3CCC

    So if the same example can be applied on 50 million records and expecting few more records getting deleted on the source table.. The scenario explained by you is very much possible..

    -Rajini

  • rajiniforu (4/6/2009)


    Hi,

    Also there could be a posibility that few records must be deleted at the source table at some point of time. Later when this table gets exported to any destination table, then that table will also miss those identity values...

    Just an example:

    1. create table iden (Number int identity(1,1), Name char(10))

    insert into iden values ('AAA')

    insert into iden values ('BBB')

    insert into iden values ('CCC')

    insert into iden values ('DDD')

    insert into iden values ('EEE')

    select * from iden

    1AAA

    2BBB

    3CCC

    4DDD

    5EEE

    Now let us delete from record from this:

    delete iden where Name like 'BBB'

    insert into iden values ('XXX')

    select * from iden

    1AAA

    6XXX

    3CCC

    4DDD

    5EEE

    2. Now we will create a new table and import the data from teh above table.

    create table idencopy2 (Number int identity(1,1), Name char(10))

    SET IDENTITY_INSERT idencopy2 ON

    Insert into idencopy2(Number,Name) (select Number,Name from iden)

    select * from idencopy2

    1AAA

    6XXX

    3CCC

    4DDD

    5EEE

    select * from idencopy2 where number between 1 and 3

    1AAA

    3CCC

    So if the same example can be applied on 50 million records and expecting few more records getting deleted on the source table.. The scenario explained by you is very much possible..

    -Rajini

    yes i have noticed that identity values seem to cary from table to table. So usually what i have to do is create a complete new table by hand and insert data manually.

  • why would an insert be rolled back? its just a basic insert

  • we don't know...it could be something in your code...maybe you have ignore_dups in your import?

    if it's from an application,say from the client-server or web and a session times out....starting a transaction and getting disconnected...there's lots of potential reasons before we ever look a tthe code itself.

    maybe you enter parent/child data, and if something is missing in the child data, you rollback?

    rajiniforu had a better explanation:

    inserts could be working 100%, but some other process is deleting records, based ob code or business needs?

    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!

  • Lowell (4/6/2009)


    we don't know...it could be something in your code...maybe you have ignore_dups in your import?

    if it's from an application,say from the client-server or web and a session times out....starting a transaction and getting disconnected...there's lots of potential reasons before we ever look a tthe code itself.

    maybe you enter parent/child data, and if something is missing in the child data, you rollback?

    rajiniforu had a better explanation:

    inserts could be working 100%, but some other process is deleting records, based ob code or business needs?

    i can assure no deletes ever happened on this table in any way shape or form ever

  • xgcmcbain (4/6/2009)


    Lowell (4/6/2009)


    we don't know...it could be something in your code...maybe you have ignore_dups in your import?

    if it's from an application,say from the client-server or web and a session times out....starting a transaction and getting disconnected...there's lots of potential reasons before we ever look a tthe code itself.

    maybe you enter parent/child data, and if something is missing in the child data, you rollback?

    rajiniforu had a better explanation:

    inserts could be working 100%, but some other process is deleting records, based ob code or business needs?

    i can assure no deletes ever happened on this table in any way shape or form ever

    By definition, identity has gaps, check BOL. If you need sequential id's without gaps, identity is not the way.

  • xgcmcbain (4/6/2009)


    why would an insert be rolled back? its just a basic insert

    Because you have duplicates in your import files 🙂

    (See post http://www.sqlservercentral.com/Forums/Topic690671-338-1.aspx)

    Every time you load a duplicate, the insert gets rolled back, but the identity value is gone forever...

  • Tim Wilson-Brown (4/6/2009)

    Every time you load a duplicate, the insert gets rolled back, but the identity value is gone forever...

    Actually SET IDENTITY_INSERT ON would let you insert values into column that has identity built. Is that what you mean ?

    Source : Click here

    ~ IM

  • sayfrend (4/6/2009)


    Tim Wilson-Brown (4/6/2009)

    Every time you load a duplicate, the insert gets rolled back, but the identity value is gone forever...

    Actually SET IDENTITY_INSERT ON would let you insert values into column that has identity built. Is that what you mean ?

    Source : Click here

    ~ IM

    No, I meant what I said 🙂

    The current Identity value stays incremented, even if the transaction that used it is rolled back.

    xgcmcbain was complaining about non-unique inserts failing in another post about the same table: http://www.sqlservercentral.com/Forums/Topic690671-338-1.aspx.

    See the explanation in BOL: http://msdn.microsoft.com/en-au/library/ms175098(SQL.90).aspx under 'Remarks'.

    Edited to give SQL 2005 BOL reference (was SQL 2008 BOL reference).

  • Tim Wilson-Brown (4/6/2009)


    xgcmcbain (4/6/2009)


    why would an insert be rolled back? its just a basic insert

    Because you have duplicates in your import files 🙂

    (See post http://www.sqlservercentral.com/Forums/Topic690671-338-1.aspx)

    Every time you load a duplicate, the insert gets rolled back, but the identity value is gone forever...

    is there a way to turn that off?

  • xgcmcbain (4/6/2009)


    Tim Wilson-Brown (4/6/2009)


    xgcmcbain (4/6/2009)


    why would an insert be rolled back? its just a basic insert

    Because you have duplicates in your import files 🙂

    (See post http://www.sqlservercentral.com/Forums/Topic690671-338-1.aspx)

    Every time you load a duplicate, the insert gets rolled back, but the identity value is gone forever...

    is there a way to turn that off?

    What do you want to turn off:

    * Rollback of Duplicate Inserts, or

    * Missing Identity Values from Failed Transactions?

    You can design your insert query to filter out duplicates.

    That will avoid both issues, and is a better design than other alternatives.

    Or you can set the unique index to ignore duplicate inserts (IGNORE_DUP_KEY = ON).

    This will avoid failures on duplicate inserts, but I'm not sure if the identity values will still be used.

    This is a poor design because it just hides the problem - in particular, it doesn't allow you to update duplicate rows with later values.

    Or you can run DBCC CHECKIDENT('table_name',RESEED) after every failed insert.

    This will allow you to reuse the 'lost' identity values from the most recent insert.

    But again this is a poor design because it just hides the problem - and could still allow gaps when rows are deleted, or a successful insert happends before you can reseed the table.

    Can you explain why your identity values need to have no gaps?

  • jgrubb (4/6/2009)


    By definition, identity has gaps, check BOL. If you need sequential id's without gaps, identity is not the way.

    Ummm.... you're gonna have to show the BOL link for that one. It is NOT by definition that an IDENTITY column will have gaps. They only occur if a rollback or deletion has occurred. From BOL...

    Note:

    If an identifier column exists for a table that has frequent deletions, gaps can occur between identity values. Deleted identity values are not reused. To avoid such gaps, do not use the IDENTITY property.

    ... and, there are very, very few good reasons to avoid gaps.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 22 total)

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