Solved

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
6
2,194 Views
Last Modified: 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.
0
Comment
Question by:FaheemAhmadGul
6 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 39948571
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
 
LVL 38

Accepted Solution

by:
Jim P. earned 100 total points
ID: 39948579
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
 

Author Comment

by:FaheemAhmadGul
ID: 39948588
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 65

Expert Comment

by:Jim Horn
ID: 39948591
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
 

Author Closing Comment

by:FaheemAhmadGul
ID: 39948597
This has worked perfectly. Many thanks.
0
 

Author Comment

by:FaheemAhmadGul
ID: 39948602
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now