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,487 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
 
LVL 1

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 66

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
 
LVL 1

Author Closing Comment

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

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

622 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