Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2003
    Posts
    223

    Unanswered: How to make indentity column continually?

    Hello, everyone:

    I have a table with an indentity column as first column. At beginning it is continue such as 0-50. I delete last 20 columns by hand. The 0-31 is left. When the new data is inserted, I hope the new indentity column begin from 32. How to do that? Now the indentity column begin from 51 as the new data is inserted.

    Thanks a lot

    ZYT

  2. #2
    Join Date
    Nov 2004
    Posts
    6
    This works for me. I'm using EM. After you delete the rows from the table, remove the IDENTITY property and save the change to the table. Then add the IDENTITY property back using the defaults (seed and increment of 1). The next row that will be added should have the next available number as it's IDENTITY value. In your case, 32.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    1. Add a new column
    2. Copy data from identity column to new column
    3. drop identity column
    4. change name of new column to old identity column
    5. make new column an identity

    BUT!

    The fact that you want to do this tells me the column should not be an identity column.

    You will forever more be worry about gaps in sequences, ect.

    The identity column shouldn't be used for "ordering" data.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Wow, Brett makes laws...

    DBCC CHECKIDENT('table_name', RESEED|NORESEED, <new_seed_value>)

    And why can't you order by identity column?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by rdjabarov
    And why can't you order by identity column?
    I guess that didn't come out right...


    They are artificially leaning on an IDENTITY Column where gaps in the sequence is a problem...if they are building a process that is dependant on the fact that there has to be a "next row", then I would consider that a bad design.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A surrogate key, whether INT or GUID, should not be relied upon for ordering data. By definition it has no inherent relationship to the data it represents.

    It's not a law. It's a principle, and a good one.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It's not the relationship to data that defines the ordering, it's the characteristics of the field. In this case if the key is clustered then the order is dictated by the value of the field, not by what data type it is or whether it has a relationship to data or not. READ THE POSTS!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Dude, I can see your veins popping from here. That can't be healthy.

    Yes, a clustered index is physically ordered. Duh.

    But it is a bad idea to depend on a surrogate key not having gaps, or even being an indication of the order the data was created. That's what the datetime datatype is for.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That's not what the topic was about, READ THE POSTS! And leave my veins alone. I am not saying anything about what's popping on your face, right?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by yitongzhang
    I hope the new indentity column begin from 32. How to do that? Now the indentity column begin from 51 as the new data is inserted.
    Read the post...ok...

    Dude...they run out of tequila in Texas?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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