Skip to main content
H
harikishorereddyc0001
April 20, 2022
Question

IDENTITY column is skipping the Sequence Number.

  • April 20, 2022
  • 8 replies
  • 0 views

Hi Team,

I am using Appian with backend SQL Server database.

I have observed that the IDENTITY column value of a table where I am storing data is skipping number sequence all of sudden. If you observe in my below attached screen, it started showing 1009 after 24.

I was unable to trace the root cause of the issue.

Can someone advice on this please. Thanks.

    8 replies

    richardm900458
    richardm900458
    April 20, 2022

    -> did somebody delete the other DB entries?
    -> got the unique key counter somehow setted to 1009?

    H
    harikishorereddyc0001Author
    April 20, 2022

    I am the one who is working on this and I am sure no one deleted the data and also not touched IDENTITY column setting.

    richardm900458
    richardm900458
    April 20, 2022

    is this a unique key?

    stefanhelzle0001
    stefanhelzle0001
    Brainy
    April 20, 2022

    In which way is it important to have no gaps?

    H
    harikishorereddyc0001Author
    April 20, 2022

    Sequence number is very much important for business. Based on that number only they are identifying the number of records submitted and also we are showing this field everywhere. 

    mikes0011
    mikes0011
    Brainy
    April 20, 2022

    Looks like the AUTO_INCREMENT value got set to something higher at some point - like if a new entry for some reason had its PK ID manually set to "1009" before being added.  I'd guess you will need to manually fix the errant entries (if safe to do so) then manually set the AUTO_INCREMENT value back to the next-higher ID.

    ALTER TABLE `TABLE_NAME` auto_increment = 35;

    The Expression Guru
    richardm900458
    richardm900458
    April 20, 2022

    that was my Idea, but does't explain why it happend in the first place. mysterious

    Terms & ConditionsAccessibility statement