Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 77
  • Last Modified:

PK numbers not follow

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
Tech_Men
Asked:
Tech_Men
5 Solutions
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Prakash SamariyaIT ProfessionalCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
funwithdotnetCommented:
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
 
Prakash SamariyaIT ProfessionalCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Tech_MenAuthor Commented:
for the help try
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now