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?
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.