Results 1 to 5 of 5

Thread: identity error

  1. #1
    Join Date
    Jan 2004

    Unanswered: identity error


    I have said yes to identity for my id in some tables. I left the values of identity seed and increment as 1, so it should really start at 1 and keep increasing by 1. When I input and then decide delete a row it affects the increment seed!!!e.g. if i delete row 2, the next row should be 2 but it becomes 3!! how do i stop this???

  2. #2
    Join Date
    Mar 2002
    Bielefeld, Germany
    That's the normal behavior. You can reset the seed with DBCC checkident,reseed (see manual), but that'll only set a new starting point and you'll keep getting "holes" when you delete rows. To prevent that, you should disable "identity" and increment manually.

  3. #3
    Join Date
    Jun 2003
    Provided Answers: 1
    Its not a good idea in general to rely on surrogate keys for sorting or ordering data. It is better to use a natural key or add a datetime value.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  4. #4
    Join Date
    Apr 2002
    Toronto, Canada
    asbirpam, my advice is, just don't worry about it

    leave the holes

    are you worried about running out of numbers?

    let's say you add new rows to your table at the rate of 100 per hour (that's more than one per minute)

    with an integer identity field, you will not run out of number for over two thousand years

    i personally would not worry about it | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2002
    Not to mention that you can use the datatype bigint (ss2k) as well for identities. So for the same 2000 year time span you could have over 500 billion transactions per hour before the identity would rollover ... Just think that when our grandchildren (future dbas) talk to us about their trillion records per hour processing we can warn them of this.
    If you do not have SQL Server Books Online (BOL) installed - please do so. The majority of questions asked in the SQL Server forum could be eliminated if people had access to bol. BOL

Posting Permissions

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