Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66

    Unanswered: are gaps possible in Identity values ?

    Hi,

    coming from a Sybase background, I'm used to identity columns having significant gaps. E.g. first few rows have 1,2,3,..... But then, the following rows could be 500001,500002,500003, etc.

    Now I'm writing a few stored procs for Sql 2000, and I need to find the __second__ record inserted. Can I rely on the Identity column simply being equal to min() + 1 ? The Sql 2000 documentation seems to suggest that, but I'm being careful, given past experiences.

    I know a work round, but would take two queries. I have a primary key on the identity column. At the beginnning of my batch job, all records are deleted from the table, then the table is populated again.

    Andrew Schonberger

  2. #2
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53
    Hi,

    as long as sql will populate the identity col it will be sequentally, until the (far) end is hit. But, for example, inserting rows with "identity off" might break this rule, so you should not rely on min()+1 for the second row.

    markus

  3. #3
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80
    The only way (that I've encountered) to get a gap between two sequential rows in your table is to insert new record(s), roll back the transaction (for some reason) and the insert a new one. In this case you will have a gap equal to the number of records that were inserted and rolled back.

    But if you say that all records in your table are deleted and inserted again in the top of your store proc in one single batch (that I supposed it's like that) than you don't have to worry, you will have a continuous flow of values for you PK.


    ionut

  4. #4
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66
    Thanks for your replies.

    My records are imported from Excel via an Access project. So, there is no single insert-select statement into my table, just a series of individual inserts.

    While I was waiting for your replies, I have written my stored proc using min() twice. It works on the safe side. Thanks again.

Posting Permissions

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