Problem In Primary Key

  • deba_20032004

    Default port

    Points: 1476

    Dear Sir,

    I am in great trouble that I have a primary key field which is data type is int and identity specified. but problem is i can't maintain sequence due to

    when I delete one record then i can't recover previous no. how to get back the previous serial no. please tell me

    Thanking you

    Debasis

  • Phil Parkin

    SSC Guru

    Points: 244664

    It shouldn't matter.

    But check out DBCC CHECKIDENT RESEED in BOL if you are adamant that it must be changed.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Identities aren't supposed to maintain a sequence without gaps. Why does it matter?

    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
  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    IDENTITY is not meant to maintain an unbroken sequence. It is only meant to quickly produce a new unique value that is higher than the last one that was used.

    If you delete a row or if an insert fails, the IDENTITY value is not reused.

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

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