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,425 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
When are cursors useful? 8 62
*** Windows Server 2012 Websites Set Up *** 17 33
ORA-01843: not a valid month - use NVL 14 21
MS SQL TO JSON 14 18
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

710 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