Solved

PK numbers not follow

Posted on 2016-08-11
8
68 Views
Last Modified: 2016-08-26
hi there
in my customer table i have CID its the PK
1
2
3
and then
3000
how i fix that the 3000 will return to be 4
and after 5-6-...
thanks...
0
Comment
Question by:Tech_Men
[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
8 Comments
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 124 total points
ID: 41751700
It's an Identity column? If it is then it's a well known bug but the jump behavior should be by 1000 and not 3000.
0
 
LVL 10

Assisted Solution

by:Prakash Samariya
Prakash Samariya earned 130 total points
ID: 41751808
Please check below code to reset Identity column values:
Read the comment above each sql statement to understand the logic
/*Store data into TEMP table*/
SELECT * 
INTO #TEMP
FROM [TESTSTAGE].[DBO].[PRODUCTDETAILS]

/*Set identity column ON (if not) in YOUR table*/
SET IDENTITY_INSERT [TESTSTAGE].[DBO].[PRODUCTDETAILS] ON

/*Truncate all data from YOUR table*/
TRUNCATE TABLE [TESTSTAGE].[DBO].[PRODUCTDETAILS]

/*Remove identity column from TEMP table*/
ALTER TABLE #TEMP DROP COLUMN PRODUCTID

/*Set identity column OFF in YOUR table*/
SET IDENTITY_INSERT [TESTSTAGE].[DBO].[PRODUCTDETAILS] OFF

/*Insert data from TEMP table into YOUR table*/
INSERT INTO [TESTSTAGE].[DBO].[PRODUCTDETAILS]
SELECT * FROM #TEMP

Open in new window

0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 123 total points
ID: 41751872
A better question is, why are you worried about it?

Relying on the fact that an identity column is sequential is not a good idea.   There are many reasons they can get out of sequence.

 As long as the data remains consistent, then it really doesn't matter what the number is.

Jim.
0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 12

Assisted Solution

by:funwithdotnet
funwithdotnet earned 123 total points
ID: 41752282
Jim Dettman makes a good point. The PK identity should not be used for real-world identity, as it can change, as you have discovered. If you need real-world identity (an unique value that is referenced outside the database. i.e customer#), it's best to add a column for that.

As to your original question, I believe you can (backup first) delete the PK int column, add it back and re-assign the PK & identity to it. It will repopulate the PK with sequential numbers.

You may need to check (in SSMS) that Tools > Options > Designers > Table and Database Designers > "Prevent saving changes that require table re-creation." is not checked.
0
 
LVL 10

Accepted Solution

by:
Prakash Samariya earned 130 total points
ID: 41771288
Tech_Men,  
Have you checked my answer [ID: 41751808] for your question!
I have tried that script and then I gave you! Please recheck it!

Note:
- MSSQL Server do have several bug including this and we have to accept that! What we can do is, create workaround to overcome from this situation! Check Vitor's post [ID:41751700]
- There is no direct way without making set of SQL statements!

Hope this could understand better!
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41771298
no one gave me a good answer
Would be good if you could give some feedback since all we spend some of our time trying to help you.
1
 

Author Closing Comment

by:Tech_Men
ID: 41771625
for the help try
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

752 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