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...
Tech_MenAsked:
Who is Participating?
 
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:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
 
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
 
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.

All Courses

From novice to tech pro — start learning today.