Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2009
    Posts
    20

    Unanswered: Consecutive Identitiy Values (was "Question again")

    Hi....
    I have a question... I made a Table..... But when i delete a record and i put new one in the field (Code ID) continue from the way i started....
    For example

    CodeID
    1
    2
    3
    4

    delete (4)
    insert
    5
    6
    I wanna put again in 4 how i can do that???????

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why do you want to reuse the value?
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2009
    Posts
    20
    mmm... i dont know... does really matter???????? just to be in order to see the user....
    1
    2
    3
    4

    and not
    1
    2
    4

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by gvee
    Why do you want to reuse the value?
    Indeed.

    Your options are:
    1) Don't delete the data; modify it

    2) Use some variant of DBCC CHECKIDENT to reset the seed value (be really careful here)

    3) If you are just loading up the table for the first time (and want a clean sequential set of values), export all columns except the ID column, truncate the table, and then re-import them (ie, copy to a different, temp table, truncate the primary table, and then copy them back)

    Think this one through... you don't want to design an application process around this (at least, I don't think you do).


    Regards,

    hmscott

    Edit: Urgh. Add truncate step.
    Have you hugged your backup today?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ThomasKain24
    I wanna put again in 4
    don't do it

    identity values are unique, by definition, and that's all you should care about

    the actual values, and whether there are gaps, should ~not~ matter

    if you think it does matter, then you will need something besides an identity column for that purpose
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by ThomasKain24
    mmm... i dont know... does really matter???????? just to be in order to see the user....
    1
    2
    3
    4

    and not
    1
    2
    4
    Never show your ID's (PK's) to users. Use another "key", a user-key, they can see. In time, they will come up with all kinds of clever tricks to make their keys "better", like make a numeric value longer or turn a numeric value to a text column or ...

    By defining user-keys as ordinary attributes that could change over time, instead of taking your database ID's (PK's), you can alter those user-keys at will without disrupting your database.

    Think about the implications when you would have to change a PK from INTEGER to BIGINT or CHAR(25) when that PK is referenced in other tables (FK).
    Compare that with the implications when you would have to change a non-PK column INTEGER to BIGINT or CHAR(25). Without that column being referenced in other tables.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •