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

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.
LVL 1
FaheemAhmadGulAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jim P.Connect With a Mentor Commented:
Below is the quick and dirty method. Essentially select every field but the ID field into a temporary table. The TRUNCATE TABLE resets the ID field. Then to insert back into the original table again specifying the columns.

SELECT Field2, Field3, Field4, Field5, ....
INTO #TempTable
FROM OrigTable
Order BY ID -- or by any other order you want.
GO
TRUNCATE  TABLE OrigTable
GO
INSERT INTO OrigTable(Field2, Field3, Field4, Field5, ....)
SELECT Field2, Field3, Field4, Field5, ....
FROM #TempTable

Open in new window

0
 
mankowitzCommented:
1. You probably don't want to do this. if you have any other tables that reference this table, all those relationships will be lost. If you want to know how many records you have, use a query with COUNT(*). It is very fast and will help you preserve referential integrity.

2. you can just remove the identity field and then insert a new one which will automatically reseed at 1

3. DBCC CHECKIDENT ("TABLE NAME", RESEED, 1);
0
 
FaheemAhmadGulAuthor Commented:
Thank you very much for your comment. I do not have any other tables that reference this table, so preserving referential integrity is not an issue in my situation. I wish to make ID field continuous for a reason other than being merely able to count the records and I wish to avoid removing the identity field and reinserting a new one.
That is why I am looking for a query that will achieve this for me without loosing any data.
I have tried
 DBCC CHECKIDENT ("TABLE NAME", RESEED, 1);
but it has not worked.
I will appreciate some further help.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Expending on mankowitz's 'You probably don't want to do this ... table references', a VERY common practice I've seen is to have two 'keys':

1 - An int identity(1,1) field that is the primary key.  All table relationships use this key.  Users do NOT refer to this key as the identifier of the row, e.g. Order 123 may not necessarily have an id=123.

2 - A column that is NOT a PK or FK in the database, but is exposed to the 'business side' so users can use this as the key, e.g. Order 123 may have id=134 and business_key=123.  We call this a 'business key'.

The reason this is done is in case the business has any goofy-riffic logic behind what they want as a key.  

In your case that would be numbers with no gaps.   This is often done by writing an insert trigger in the table that finds the lowest positive number in the business_key field, and assigns it that.

 In other cases that may be a two-letter state code followed by a number, such as CA001, NY001, GA 001, CA002, etc.
0
 
FaheemAhmadGulAuthor Commented:
This has worked perfectly. Many thanks.
0
 
FaheemAhmadGulAuthor Commented:
Thank you very much Jim for expanding on Mankowitz's comments. What you have said will help my understanding further. I had already accepted Jim P's solution (which solved my problem in the way I wanted to solve it at this point) but I am grateful for your additional comments also. Greatly appreciated. Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.