Large gap between two int primary key column SQL

Question

0.00/5 (No votes)

See more:

i have noticed that in my 2, 3 programs, the primary key of every table jumped from like 9 to 1006, all almost similar jumps. while this column is autoincremented. what is the exact problem and how to handle. and is it any harmful?
a case is

ID       date     custID invoice payment  arrears
59      12/3/2017   1      68       0.5    667
60      12/3/2017   1      51       0      718
61      12/3/2017   1      51       0      769
1056    12/4/2017   1      13       0      782
1057    12/4/2017   1      575      0      1357

What I have tried:

i tried research but nothing found help.

Posted 4-Dec-17 7:54am

Arham Anees

Updated 5-Dec-17 4:50am

Add a Solution

2 solutions

Solution 1

The chances are that you have added the rows, and subsequently deleted them: SQL doe not "reuse" numbers in an IDENTITY column so when you delete rows that you get gaps in the sequence that will not be filled.
That's by design, so that data in other tables which refers to the "main table" via the ID do not associate the wrong data with the "new" row, even if a formal FOREIGN KEY relationship has not been established.

If you need a sequence that always has no gaps, then look at ROW_NUMBER (Transact-SQL) | Microsoft Docs[^] - but that does not uniquely identify a row, except in the context of a specific SELECT statement and should not be used as an ID for that reason.

Posted 4-Dec-17 8:05am

OriginalGriff

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)

Richard Deeming · Accepted Answer · 2017-12-05T04:50:00

Solution 2

The most likely cause is that your server was restarted:
Failover or Restart Results in Reseed of Identity | Microsoft Connect[^]

This behaviour is apparently "by design".

For SQL 2014 or later, adding trace flag -T272 is supposed to resolve the problem.

Posted 5-Dec-17 4:50am

Richard Deeming

Comments

Arham Anees 8-Dec-17 10:50am

this answer sounds good to me but one confusion what if i restart my computer and connection is given from some desktop application, will it make problem?

Richard Deeming 8-Dec-17 10:53am

If you restart the server, then desktop applications won't be able to connect whilst it's restarting. But once it's back up and running, they should reconnect without any problems.

Arham Anees 8-Dec-17 11:07am

link for adding trace flag
http://sql-articles.com/articles/general/enable-disable-trace-flags-in-sql-server/