Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135

    Unanswered: Row in tables jumps from 3 to 5?

    Hi all,

    Quick question which i hope you can help with?

    I have a table in sql server 2005 that holds a unique number that is auto incremented against all new entries. So, entry 1 is set to the unique key 1, entry 2 is set to 2, entry 3 to 3, but entry 4 to 5.

    I was was playing about with the database and deleted one of the rows (4) and now the auto incrementing number runs like this:

    1 = 1
    2 = 2
    3 = 3
    5 = 4
    6 = 5
    7 = 6

    And now when I want to display the information in an ASP page, the two obviously dont match, is there any way i can resolve this issue so everything is once more in sync?

    Hope this all makes sense, if not please shout.

    Kind regards
    MG

  2. #2
    Join Date
    Jun 2009
    Posts
    66
    Use a row_number query, IDENTITY fields can have gaps (deletes, constraints can cause gaps).

  3. #3
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi sqlguru, thanks for the reply.

    Im new to SQL, but will look into this. Thank you for steering me in the right direction.

    regards
    MG

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    produce your row number with ASP as you loop over the returned result set from the query

    much more efficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I believe that row_number is pretty well zero cost if it is ordered the same as the resultset (assuming the resultset is ordered of course).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi,

    Yes the result-set is ordered

    regards
    MG

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why do you want to display an arbitrary number?

    Not even the surrogate key

    Got any natural keys (unique index)?
    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.

  8. #8
    Join Date
    Jun 2009
    Posts
    66
    Quote Originally Posted by r937
    produce your row number with ASP as you loop over the returned result set from the query

    much more efficient

    So you are telling me that returning 5 million records to the application layer just so you can "iterate" over it is more efficient?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by sqlguru
    So you are telling me that returning 5 million records to the application layer just so you can "iterate" over it is more efficient?
    straw man, and a weak one too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi all,

    There seems to be alot to take in, a lot of new terminology / information for a newbie, if only you could see my confused face right now, lol.

    Just like to say thanks for all your post so far, but I have decided to drop it and move on to another task that has been presented to me, as it needs to be completed by the end of the month - and yes im stuck with that too.

    Onwards and sidewards!

    Regards
    MG

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I once new a SQL Guru

    Well Grapes...post a new thread for any other help

    OnWard and DownWard
    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
  •