Post reply

IDENTITY(1,1) lost numbers

  • January 19, 2009 at 9:55 am

    #217599

    Can you help me, please?

    I created a table with this:

    id_form int IDENTITY(1,1) NOT NULL,

    PRIMARY KEY (id_form)

    SELECT COUNT(*)

    FROM table

    UNION

    SELECT MAX(id_form)

    FROM table

    The results are:

    622

    688

    Why didn't generate some id's?

    For example:

    from id_form 83 jump to 85, what happened with 84.

    Thanks for your attention.

  • January 19, 2009 at 10:01 am

    #929297

    maybe the rows were deleted

  • January 19, 2009 at 10:02 am

    #929298

    That'll happen any time an insert doesn't succeed (due to a constraint violation, a datatype conversion error or a trigger rollback) or when rows are deleted. The id will be assigned as the insert starts, and the current identity seed incremented. If the insert doesn't succeed, the identity seed is not decremented again (there may already have been another insert)

    It's normal with an identity column

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 19, 2009 at 2:01 pm

    #929375

    Thanks.

    GilaMonster (1/19/2009)

    That'll happen any time an insert doesn't succeed (due to a constraint violation, a datatype conversion error or a trigger rollback) or when rows are deleted. The id will be assigned as the insert starts, and the current identity seed incremented. If the insert doesn't succeed, the identity seed is not decremented again (there may already have been another insert)

    It's normal with an identity column

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

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