Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
2,560 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 400 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

722 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