Click here to Skip to main content
15,849,129 members
Please Sign up or sign in to vote.
5.00/5 (3 votes)
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
170139
170140
170141
170142
180141
180142
180143
180144
190141
190142
190143
190144


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

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
 
Share this answer
 
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
You might find your answer here..

[^]
 
Share this answer
 
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
 
Share this answer
 
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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900