Post reply

Identity column increaments randomly

  • January 12, 2004 at 12:28 pm

    #64997

    Hi ,

    I have sql 2000 db which has identity column in a table. It has been fine for a while but in few months the identity column value is increments abnormally with out any clue, some it increaments with 2, 4 , 7 .....

    Please help me or give a clue what would have happend.

    Thanks

    venkat

     

     

     


    venkatesh

  • January 12, 2004 at 1:26 pm

    #489630

    Define the identity property is no guarantee for a gapless sequence.

    CREATE TABLE MyIdentity(

    id INT IDENTITY(1,1) NOT NULL,

    some_other VARCHAR(10)

    )

    GO

    BEGIN TRAN

    INSERT INTO MyIdentity VALUES('Erster')

    COMMIT TRAN

    BEGIN TRAN

    INSERT INTO MyIdentity VALUES('Zweiter')

    ROLLBACK TRAN

    BEGIN TRAN

    INSERT INTO MyIdentity VALUES('Dritter')

    COMMIT TRAN

    SELECT * FROM MyIdentity

    DROP TABLE MyIdentity

    GO

    ergibt einen Output von

    id          some_other

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

    1           Erster

    3           Dritter

    (2 row(s) affected)

    Can this also be your point?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 12, 2004 at 8:21 pm

    #489667

    And to add to Frank's post, deleted rows would cause the appearance of gaps.

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

    Ray Higdon MCSE, MCDBA, CCNA

  • January 13, 2004 at 12:17 am

    #489678

    In addition to Ray's addition   :

    Having more Deadlocks lately ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • January 13, 2004 at 1:04 am

    #489682

    Also, if any any inserts are done to your table, and the transaction is rolled back, the identities don't get re-used and will therefore cause gaps.

     

Viewing 5 posts - 1 through 4 (of 4 total)

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