Solved

PK numbers not follow

Posted on 2016-08-11
8
64 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 47

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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 47

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Get the latest status 8 32
Can > be used for a Text field 6 49
sql 2008 how to table join 2 17
SQL Query Help Top 1 and Distinct? 6 27
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

832 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