Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2008
    Location
    Florida, USA
    Posts
    9

    Unanswered: Primary Key / ID question

    Im a very junior admin for SQL Server 2008R2 (x64)

    I recently uploaded a table that is editable via an internal website. My issue is: If I add a record, the primary key goes up by 1. If I have 95 records, add one I get 96. If I delete that record (96) and add a new record to the table the primary key goes to 97 rather than using 96 (the now empty key).

    How do I force SQL server to use the next available number as the primary key and not skip over deleted rows of data?

    Thanks.
    Last edited by steve841; 11-20-14 at 14:53.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Short answer: You don't.

    Longer answer: The identity column is built for speed, rather than for contiguousness. If you had a process that was looking to insert hundreds of thousands of records each hour, you would not want the system asking if any records had been deleted before. Besides, suppose instead of deleting record 96, someone deleted record 50? Would the new record really need to be inserted halfway back in the table?

    Ask yourself why you want the records to have contiguous identity column values. Odds are you are displaying them to an end user. An end user will consider the gaps "ugly", while a computer has no opinion one way or the other. If you really want to show a user no gaps, you can use the ROW_NUMBER function when you get the data for the end user.

  3. #3
    Join Date
    Jun 2008
    Location
    Florida, USA
    Posts
    9
    Got it.

    Appreciate the reply.
    Steve

Posting Permissions

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