Solved

PK numbers not follow

Posted on 2016-08-11
8
60 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
8 Comments
 
LVL 46

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 8

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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 8

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 46

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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…

919 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now