How to reset the primary key field (ID ) in an SQL Table to so that it starts from 1 and there are no missing IDs
Posted on 2014-03-23
I have an SQL Server 2012 Express table say with 10 records. At one point these records had a continuous series of numbers in the Primary ID key field which is set to auto increment and their IDs were (1, 2, 3, 4, 5, 6, 7, 8, 9, 10). Over the course of time I have deleted some records say records with ID 3 and 7. Now this table has records ( 1, 2, 4, 5, 6, 8, 10) and if I add a new record to this table that will have the ID of 11.
I need a help with an SQL query that will reset ID field such that now (after I have deleted records 3 and 7) the IDs of the remaining records become 1, 2, 3, 4, 5, 6, 7, so that if I add a new record in this table it will have an ID 8 (that is the ID of the last record in the table will be equal to the total number of records in the table.
The actual table has several thousand records.
Thank you for your help in anticipation.