Post reply

Primary Key TSQL

  • March 30, 2012 at 8:47 am

    #391912

    :alien:Hello,

    I have Primary Key(called id) into one of the table which is auto increment. we have added five records from front end ehich is fine eg.

    id - 1,2,3,4,5

    now if we delete no 5 record and add new record then it will be

    id - 1,2,3,4,6

    Is there anyway can id (Primary Key) be refreshed so it can have id to 5 rather then 6.???

  • March 30, 2012 at 8:51 am

    #1466994

    Yes, but why? Gaps are a normal part of identity columns, any time an insert fails or a row is deleted there will be a gap. The identity can be reset but it is not recommended that people do so as part of normal operations.

    What happens if row 3 is deleted then a new row added? Do you want the new row to be 3 or 6?

    p.s. This is nothing to do with the primary key. It's how the Identity column attribute works

    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

  • March 30, 2012 at 9:00 am

    #1467003

    Thanks for useful informations!

    Gaps are normal then it's fine, i thought i was missing something.

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

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