Primary key column is incremented by wrong increment value

Question

5.00/5 (3 votes)

See more:

SQL-Server-2012

Hello ,

I am facing a weird issue with tables in my database.
Tables in my application are created with a primary key defined (with Clustered index).
Identity seed is a one for all tables in my database (IDENTITY(1,1)).

CRUD operations are done on these databases using stored procedure which are called from an ADO.net windows application. The problem i am facing here is sometimes values of primary key is incremented by 1000 or 10000 and i not able to figure out why.
here are some tables
for Ex. here after values 170142 , 180141 is used by SQL server or sometime after 5025 vale of primary key 6025 is choosen by SQL

VB

I have checked identity seed and increment values for these tables but it seems to be in order. and none of the in between records are deleted.

Have anybody encountered similar scenarios?
Your help is much appreciated.
Thanks.

Posted 13-Aug-14 0:46am

Amol_B

Add a Solution

4 solutions

Solution 1

Never seen or heard of that as a problem: and SQL Identity fields are used so often I'm pretty sure it would show up.

Are you sure that is exactly what is happening? It would be easy to see that kind of change if rows are deleted for example, since the identity values will not be reused.
Insert 5 rows:

SQL

INSERT INTO MyTable (desc) VALUES ('One') ('Two') ('Three') ('Four') ('Five')

Gives you:

1 One
2 Two
3 Three
4 Four
5 Five

Then delete a couple:

SQL

DELETE FROM MyTable WHERE id IN (2, 4, 5)

And add a couple more:

SQL

INSERT INTO MyTable (desc) VALUES ('New') ('And again')

And you end up with:

1 One
3 Three
6 New
7 And again

Posted 13-Aug-14 1:08am

OriginalGriff

Comments

Amol_B 13-Aug-14 7:40am

Thanks for Reply. Yes, i am sure about scenarios happening,it is not deleted row scenarios.
In my case 'new again' row will have key value as 1007

Amol_B 13-Aug-14 7:58am

Got this Article on CP itself..
http://www.codeproject.com/Tips/668042/SQL-Server-2012-Auto-Identity-Column-Value-Jump-Is

Solution 2

Not Sure but it happens if you are trying to insert a data and sql error occurred while inserting the data the identity get used. Next time when you insert it uses the next identity no.

But this reason can explain missing of 1 or 2 identity entries.

Posted 13-Aug-14 1:31am

Gupta Poonam

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gupta Poonam · Accepted Answer · 2014-08-13T01:36:00

Solution 3

You might find your answer here..

[^]

Posted 13-Aug-14 1:36am

Gupta Poonam

Comments

Amol_B 13-Aug-14 7:58am

Thanks a lot... Exact one..

below Code project Article Explains it
http://www.codeproject.com/Tips/668042/SQL-Server-2012-Auto-Identity-Column-Value-Jump-Is

Amol_B · Accepted Answer · 2014-08-13T02:07:00

Solution 4

SQL Server 2012 Auto Identity Column Value Jump Issue[^]

Posted 13-Aug-14 2:07am

Amol_B